Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to return an array from a PL/SQL Stored function

843859Apr 1 2009 — edited Apr 1 2009

Hi,

I'm having trouble passing arrays to and from pl/sql functions. I followed the instructions on http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jdbc/varray/index.html and it works just fine as is. The problem starts when I try placing the array type and the function inside a package.

Oracle:

CREATE OR REPLACE PACKAGE TEST
IS
TYPE EMPARRAY is VARRAY(20) OF VARCHAR2(30);
...
<function>
...
END TEST;
/

Java:

...
stmt.registerOutParameter( 1, OracleTypes.ARRAY,"TEST.EMPARRAY" );
...

Error:

Exception in thread "main" java.sql.SQLException: invalid name pattern: TEST.EMPARRAY
	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
	at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:527)
	at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:407)
	at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1952)
	at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:199)
	at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:118)
	at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:176)

We have some restrictions creating types and they must be in packages in our case. Please help

Thanks!

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 29 2009

Added on Apr 1 2009

#java-database-connectivity-jdbc

2 comments

309 views