JDBC Call to Oracle Stored Procedure with parameters of type PL SQL table

Question

I need to make JDBC call to a procedure with parameters of type PL/SQL table. I am trying with struct object. But I am not doing some thing correct. I get the error: ORA-04043: object "scott"."objListStruct" does not exist.

Here is the code snippet:

conn = Application.getDBConnection();
                CallableStatement cStmt = null;
                cStmt= conn.prepareCall("{call package1.procedure1"+"(?)}");
                Struct objListStruct = conn.createStruct("objListStruct",
                                objNameArr.toArray());
                cStmt.setObject(1, objListStruct,Types.STRUCT);

The parameter, "?" for this procedure, is of type:

TYPE t_name IS TABLE OF TABLE1.name%TYPE

Any insight to make this work is highly appreciated. Thanks

Solution 1

String[] varStrArr = varArr.toArray(new String[compNameArr.size()]);
OracleCallableStatement cStmt = (OracleCallableStatement) 
conn.prepareCall("BEGIN SCHEMA.PACKAGE.procedure(?);END;");
cStmt.setPlsqlIndexTable(1,varStrArr,varStrArr.length,varStrArr.length,
                        OracleTypes.VARCHAR, 50);
cStmt.execute();

The above code works. More reference material is available at: Binding IN Parameters

Solution 2

Two things:

1) The easiest way is to not pass anything to the procedure. Create a global temporary table, insert all the data you need, then call the proc, which reads from your temporary table. Just be careful that you aren't autocommitting your connection.

2) If you have to pass in an array, you'll need to drop down and use the Oracle Array type. The Oracle array type will bind to the table type. So something similar (NOTE: code not tested!) to this:

Object[] arrayObject = { x, y };

ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor(
        "MY_SCHEMA.MY_ARRAY_TYPE", conn);
ARRAY myArray = new ARRAY(descriptor, conn, arrayObject);


CallableStatement cs = conn
        .prepareCall("{ call package1.procedure1(?)}");
cs.setArray(1, myArray);
cs.execute();
conn.close();