How to pass null or empty array via setPlsqlIndexTable
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();