aspose file tools*
The moose likes JDBC and the fly likes Oracle custom object from java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Oracle custom object from java" Watch "Oracle custom object from java" New topic
Author

Oracle custom object from java

Joydeep Bhattacharjee
Greenhorn

Joined: Jan 24, 2012
Posts: 7
I have to call a stored procedure in oracle from java for which some of the parameters are oracle custom objects.

The stored procedure :-


where p_account_rec is a oracle custom type ACCOUNT_REC_TYPE


ACCOUNT_REC_TYPE is defined in package HZ_CUSTOMER_ACCOUNTS_PUB as




Now the java code for calling the stored procedure :





Exception in thread "main" java.sql.SQLException: invalid name pattern: HZ_CUSTOMER_ACCOUNTS_PUB.ACCOUNT_REC_TYPE
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:412)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:327)
at oracle.sql.StructDescriptor.initPickler(StructDescriptor.java:206)
at oracle.sql.StructDescriptor.<init>(StructDescriptor.java:179)
at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:142)
at com.teliasonera.fi.ebshla.cust.impl.StoreHzCommand.main(StoreHzCommand.java:250)








Please can you suggest where I am going wrong ...
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3456
    
  47

The object type you want to use in Java must be declared as SQL type (using the CREATE TYPE SQL statement). You've declared it in a package, which is PL/SQL. PL/SQL types are not accessible from Java.

There might be other problems related to properly registering the type in Java (it might be necessary to use fully qualified name of the type in Java, even if it is in your current schema), and also you should make sure there is only one such type defined -- if your application uses several schemas, make sure your type is created in only one of them. But basically this should be all.
Joydeep Bhattacharjee
Greenhorn

Joined: Jan 24, 2012
Posts: 7
Can you please tell where the package should be defined , is it that it can be accessed if it is under the schema and not in any packages



I have used the fully qualified name of the type in Java APPS.HZ_CUSTOMER_ACCOUNTS_PUB.account_rec_type but the exception occours.

Exception in thread "main" java.sql.SQLException: ORA-01948: identifier's name length (41) exceeds maximum (30)
ORA-06512: at "SYS.DBMS_PICKLER", line 18
ORA-06512: at "SYS.DBMS_PICKLER", line 58
ORA-06512: at line 1

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:184)
at oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(T4CCallableStatement.java:873)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1086)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2984)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3076)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4273)
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:404)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:327)
at oracle.sql.StructDescriptor.initPickler(StructDescriptor.java:206)
at oracle.sql.StructDescriptor.<init>(StructDescriptor.java:179)
at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:142)
at com.teliasonera.fi.ebshla.cust.impl.StoreHzCommand.main(StoreHzCommand.java:250)


We have only one schema and I am sure that there is only one Type defined with account_rec_type.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3456
    
  47

Joydeep Bhattacharjee wrote:
Where the package should be defined , can I access it , if it is not in any package but under schema directly (using the CREATE TYPE SQL statement)

Yes, you can access it. SQL types are available in PL/SQL, including packages and procedures, but not the other way around (ie. PL/SQL types, including BOOLEAN datatype, cannot be used in SQL). And Java can access only SQL types.


I have used the fully qualified name of the type in Java APPS.HZ_CUSTOMER_ACCOUNTS_PUB.account_rec_type but the exception occours.

Fully qualified name is another requirement, independent of the SQL type one. You must use both SQL type and fully qualified name of that type in Java for your code to work. You're still trying to register a PL/SQL type (it is part of a package), and that's not going to work.
Joydeep Bhattacharjee
Greenhorn

Joined: Jan 24, 2012
Posts: 7
Thanks for your explanation , it was helpful indeed . But there seems to be some restriction on the length of name createDescriptor(String name, java.sql.Connection conn). It seems maximum allowed length is 30 . When I use the fully qualified name which has length 41 I get the following exception ...

xception in thread "main" java.sql.SQLException: ORA-01948: identifier's name length (41) exceeds maximum (30)
ORA-06512: at "SYS.DBMS_PICKLER", line 18
ORA-06512: at "SYS.DBMS_PICKLER", line 58
ORA-06512: at line 1

However I think we will create SQL types as you have suggested and try to access it .
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3456
    
  47

The error is caused by your attempt to register PL/SQL type instead of SQL type. The PL/SQL type name, including package name, has 41 characters (HZ_CUSTOMER_ACCOUNTS_PUB.account_rec_type). Oracle does not expect you to register PL/SQL type and therefore it only parses the schema name (APPS) out, and expects the rest to be the SQL type name (I'm just guessing here). Identifiers in Oracle (except in Java stored procedures) are limited to 30 characters in length, and the type name you're trying to register is longer than that, but only because it contains two identifiers instead of the (expected) one.

If you create and register the SQL type, it's name will be limited to 30 characters and this error will not occur, since the package name won't be part of the fully qualified type name.

Admittedly, the error message is a bit misleading, but I've already said you twice it cannot work with package-defined types....
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Oracle custom object from java
 
Similar Threads
invalid name pattern
Oracle Stored Procedure Problem
Retrieving oracle collections with jdbc and jpub generated classes
Error while callling stored procedure : Non supported SQL92 token
passing arrays to oracle