Skip to Main Content

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 pass null or empty array via setPlsqlIndexTable

sto-OracleOct 27 2008 — edited Nov 20 2014
How do I pass an empty array or null via setPlsqlIndexTable? I am using 11.1.0.6.0 JDBC thin driver:

Here's my test:
For the test package:
create or replace package index_by_table_test as
type mytab is table of number index by pls_integer;
procedure do_stuff(intab mytab);
end;
/

create or replace package body index_by_table_test as
procedure do_stuff(intab mytab) is
begin
for i in 1..intab.count loop
dbms_output.put_line(intab(i));
end loop;
end;
end;
/

For the java client:
This works:

ps = (OracleCallableStatement) mConn.prepareCall("begin index_by_table_test.do_stuff(:1); end;");
int[] blah = {1};
ps.setPlsqlIndexTable(1, blah, blah.length, blah.length, OracleTypes.NUMBER, 100);
ps.execute();
System.out.println("Success");

Using setNull fails with Exception java.sql.SQLException: Unsupported feature: sqlType=-14
ps = (OracleCallableStatement) mConn.prepareCall("begin index_by_table_test.do_stuff(:1); end;");
ps.setNull(1, OracleTypes.PLSQL_INDEX_TABLE);

Using a null array fails a different Exception PLS-00306: wrong number or types of arguments in call to 'DO_STUFF'
ps = (OracleCallableStatement) mConn.prepareCall("begin index_by_table_test.do_stuff(:1); end;");
int[] blah = null;
ps.setPlsqlIndexTable(1, blah, 0, 0, OracleTypes.NUMBER, 100);
ps.execute();

Using an empty array also fails with Exception PLS-00306: wrong number or types of arguments in call to 'DO_STUFF'
ps = (OracleCallableStatement) mConn.prepareCall("begin index_by_table_test.do_stuff(:1); end;");
int[] blah = new int[0];
ps.setPlsqlIndexTable(1, blah, 0, 0, OracleTypes.NUMBER, 100);
ps.execute();
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 25 2008
Added on Oct 27 2008
5 comments
1,995 views