3

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();
MiketheCalamity
  • 1,229
  • 1
  • 15
  • 32
  • You might find this question http://stackoverflow.com/q/7878735/272735 useful. Please also show us the definitions of the arrays (e.g. `AssocArrayVarchar20_t`). – user272735 Aug 22 '15 at 06:55

2 Answers2

3

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);
Vadim Kirilchuk
  • 3,532
  • 4
  • 32
  • 49
  • I have used createARRAY() also to pass a Java array to a PL/SQL stored procedure that has a PL/SQL table as input parameter. It works. Also note that Oracle ARRAY class implements the standard java.sql.Array interface so you can write `Array array = oracle.jdbc.OracleConnection.createARRAY(sql_type_name, elements);` and the only non-standard object you have to use is OracleConnection. – Pino Aug 09 '16 at 07:39
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.

rgettman
  • 176,041
  • 30
  • 275
  • 357
  • I'm getting "wrong number or types of arguments in call to 'DATA_WRITE'" but I have all 3 inputs and I think they're the right types. See code above. The stored procedure does use VARCHAR2 maybe that's the problem? But there is no `OracleTypes.VARCHAR2`. – MiketheCalamity Aug 24 '15 at 18:17
  • Scratch that last comment, I misspelled my Stored Proc – MiketheCalamity Aug 24 '15 at 18:42