使用 PL SQL 表类型的参数对 Oracle 存储过程的 JDBC 调用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10711446/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
JDBC Call to Oracle Stored Procedure with parameters of type PL SQL table
提问by Kumar S
I need to make JDBC call to a procedure with parameters of type PL/SQL table. I am trying with struct object. But I am not doing some thing correct. I get the error: ORA-04043: object "scott"."objListStruct" does not exist.
我需要使用 PL/SQL 表类型的参数对过程进行 JDBC 调用。我正在尝试使用 struct 对象。但我没有做正确的事情。我收到错误消息：ORA-04043：对象“scott”。“objListStruct”不存在。
Here is the code snippet:
这是代码片段：
conn = Application.getDBConnection();
CallableStatement cStmt = null;
cStmt= conn.prepareCall("{call package1.procedure1"+"(?)}");
Struct objListStruct = conn.createStruct("objListStruct",
objNameArr.toArray());
cStmt.setObject(1, objListStruct,Types.STRUCT);
The parameter, "?" for this procedure, is of type:
参数“？” 对于此程序，类型为：
TYPE t_name IS TABLE OF TABLE1.name%TYPE
Any insight to make this work is highly appreciated. Thanks
任何使这项工作的见解都受到高度赞赏。谢谢
回答by Nick
Two things:
两件事情：
1) The easiest way is to not pass anything to the procedure. Create a global temporary table, insert all the data you need, then call the proc, which reads from your temporary table. Just be careful that you aren't autocommitting your connection.
1) 最简单的方法是不向程序传递任何东西。创建一个全局临时表，插入你需要的所有数据，然后调用 proc，它从你的临时表中读取。请注意不要自动提交连接。
2) If you haveto pass in an array, you'll need to drop down and use the Oracle Array type. The Oracle array type will bind to the table type. So something similar (NOTE: code not tested!) to this:
2) 如果必须传入数组，则需要下拉并使用 Oracle Array 类型。Oracle 数组类型将绑定到表类型。所以类似（注意：代码未测试！）与此类似：
Object[] arrayObject = { x, y };
ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor(
"MY_SCHEMA.MY_ARRAY_TYPE", conn);
ARRAY myArray = new ARRAY(descriptor, conn, arrayObject);
CallableStatement cs = conn
.prepareCall("{ call package1.procedure1(?)}");
cs.setArray(1, myArray);
cs.execute();
conn.close();
回答by Kumar S
String[] varStrArr = varArr.toArray(new String[compNameArr.size()]);
OracleCallableStatement cStmt = (OracleCallableStatement)
conn.prepareCall("BEGIN SCHEMA.PACKAGE.procedure(?);END;");
cStmt.setPlsqlIndexTable(1,varStrArr,varStrArr.length,varStrArr.length,
OracleTypes.VARCHAR, 50);
cStmt.execute();
The above code works. More reference material is available at: Binding IN Parameters
上面的代码有效。更多参考资料位于： 绑定 IN 参数
