oracle java - 在oracle存储过程中传递数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1399750/
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
java - passing array in oracle stored procedure
提问by coder
I have a Java app accessing an oracle stored procedure. The arguments to the stored procedure include an array type. I do it like the following...
我有一个访问 oracle 存储过程的 Java 应用程序。存储过程的参数包括数组类型。我这样做如下...
con = this._getConnection();
Connection narrowdConn = (Connection)WSJdbcUtil.getNativeConnection( (WSJdbcConnection)con );
callable = con.prepareCall("{call MY_PKG.MY_PROCEDURE(?, ?)}");
ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("VARCHAR2_ARR", narrowdConn);
ARRAY arrayArg1 = new ARRAY(arrayDescriptor, con, docNames);
ARRAY arrayArg2 = new ARRAY(arrayDescriptor, con, docTypes);
callable.setArray(1, arrayArg1);
callable.setArray(2, arrayArg2);
callable.execute();
Now, I am getting this Exception...
现在，我收到此异常...
java.sql.SQLException: invalid name pattern: MY_PKG.VARCHAR2_ARR
VARCHAR2_ARR is a public TYPE, defined inside an Oracle Package like the following:
VARCHAR2_ARR 是一个公共类型，在 Oracle 包中定义，如下所示：
TYPE VARCHAR2_ARR IS TABLE OF VARCHAR2(50);
类型 VARCHAR2_ARR 是 VARCHAR2(50) 表；
And used as such in my stored proc...
并在我的存储过程中使用...
PROCEDURE MY_PROCEDURE
(V_ARR_ARG1 IN VARCHAR2_ARR,
V_ARR_ARG2 IN VARCHAR2_ARR)
回答by Vincent Malgrat
the type VARCHAR2_ARRis a PLSQL type, you won't be able to interface it directly from java. I suggest you look into this thread on AskTomregarding a similar question.
该类型VARCHAR2_ARR是 PLSQL 类型，您将无法直接从 java 连接它。我建议您在 AskTom 上查看有关类似问题的此主题。
Here are a couple suggestions:
这里有一些建议：
- create a SQL TYPE that you can bind from java
- insert into a temporary table from java and read from it in plsql
- 创建一个可以从 java 绑定的 SQL TYPE
- 从java插入临时表并在plsql中从中读取
In both cases you will have to either modify the PLSQL procedure or add a new translation procedure.
在这两种情况下，您都必须修改 PLSQL 过程或添加新的转换过程。
回答by Vincent Malgrat
We need to set accessToUnderlyingConnectionAllowed falg true while creating a datasource
我们需要在创建数据源时设置 accessToUnderlyingConnectionAllowed falg true
