java - Calling a PL/SQL Stored Procedure With Arrays

Question

I have a PL/SQL stored procedure similar to the following that I need to call in Java:

TYPE AssocArrayVarchar20_t   is table of VARCHAR2(20)   index by BINARY_INTEGER
TYPE AssocArrayVarchar4100_t is table of VARCHAR2(4100) index by BINARY_INTEGER
TYPE AssocArrayNumber_t      is table of NUMBER         index by BINARY_INTEGER

PROCEDURE DATA_WRITE( I_NAME IN AssocArrayVarchar20_t,
                      I_NUM  IN AssocArrayNumber_t,
                      I_NOTE IN AssocArrayVarchar4100_t)
    // Do Stuff
END DATA_WRITE;

I tried the following in Java:

CallableStatement stmt = conn.prepareCall("begin DATA_WRITE(?, ?, ?); end;");
stmt.setArray(0, conn.createArrayOf("VARCHAR", new String[]{ name }));
stmt.setArray(1, conn.createArrayOf("NUMBER", new Integer[]{ num }));
stmt.setArray(2, conn.createArrayOf("VARCHAR2", new String[]{ notes }));
stmet.execute;

When I do this I get a SQLException: Unsupported Feature" on the createArrayOf() method. I've also tried setObject() and inside of createArrayOf: "varchar", "AssocArrayVarchar20_t", "varchar_t". Nothing seems to change that outcome.

Does anyone know what I'm doing wrong? I can't seem to get it to work.

UPDATE: Success!

OracleCallableStatement pStmt = (OracleCallableStatement) conn.prepareCall("begin DATA_WRITE(?, ?, ?); end;");
pStmt.setPlsqlIndexTable(1, new String[]{ name }, 1, 1, OracleTypes.VARCHAR, 20);
pStmt.setPlsqlIndexTable(2, new Integer[]{ num }, 1, 1, OracleTypes.NUMBER, 0);
pStmt.setPlsqlIndexTable(3, new String[]{ notes }, 1, 1, OracleTypes.VARCHAR, 4100);
pStmt.execute();

Answer 1

Here is an official guide reference to pass Arrays in case you need to pass arrays and not tables: oracle guide

Oracle JDBC does not support the JDBC 4.0 method createArrayOf method of java.sql.Connection interface. This method only allows anonymous array types, while all Oracle array types are named. Use the Oracle specific method oracle.jdbc.OracleConnection.createARRAY instead.

Passing an Array to a Prepared Statement

Pass an array to a prepared statement as follows.

Note: you can use arrays as either IN or OUT bind variables. Define the array that you want to pass to the prepared statement as an oracle.sql.ARRAY object.

ARRAY array = oracle.jdbc.OracleConnection.createARRAY(sql_type_name, elements); sql_type_name is a Java string specifying the user-defined SQL type name of the array and elements is a java.lang.Object containing a Java array of the elements.

Create a java.sql.PreparedStatement object containing the SQL statement to be run.

Cast your prepared statement to OraclePreparedStatement, and use setARRAY to pass the array to the prepared statement.

(OraclePreparedStatement)stmt.setARRAY(parameterIndex, array); parameterIndex is the parameter index and array is the oracle.sql.ARRAY object you constructed previously.

Run the prepared statement.

Note: by the

ARRAY array = oracle.jdbc.OracleConnection.createARRAY(sql_type_name, elements); 

They mean:

java.sql.Connection connection = ...
oracle.jdbc.OracleConnection oracleConnection = connection.unwrap(OracleConnection.class);
ARRAY array = oracleConnection.createARRAY(sql_type_name, elements); 

Answer 2

The createArrayOf method was introduced in Java 1.6, but to the best of my knowledge it doesn't handle Oracle's PL/SQL associative arrays. If you have the Oracle JDBC driver, then you have access to the oracle.sql classes.

You should be able to downcast the CallableStatement to an OracleCallableStatement. From there you can call the setPlsqlIndexTable method and you should be able to pass in a Java array.

Binds a PL/SQL index-by table parameter in the IN parameter mode.