我有一个类似于以下代码的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();