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();
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.
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);
