java - 使用数组调用 PL/SQL 存储过程

Question

我有一个类似于下面的 PL/SQL 存储过程，我需要用 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;

我在 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;

当我这样做时，我在 createArrayOf() 方法上得到了一个 SQLException: Unsupported Feature"。我也试过 setObject() 和 createArrayOf 内部:"varchar"、"AssocArrayVarchar20_t"、"varchar_t"。似乎没有任何变化那个结果。

有谁知道我做错了什么？我似乎无法让它工作。

更新:成功!

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

这是传递数组的官方指南引用，以防您需要传递数组而不是表格: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.

注:由

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

他们的意思是:

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