• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle custom object from java

 
Joydeep Bhattacharjee
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 3751
62
Chrome Netbeans IDE Oracle
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 3751
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 3751
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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....
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic