java - 在oracle存储过程中传递数组 [英] java - passing array in oracle stored procedure

问题描述

我有一个访问 oracle 存储过程的 Java 应用程序.存储过程的参数包括数组类型.我喜欢以下...

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...

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 是一个公共类型，在 Oracle 包中定义如下:

VARCHAR2_ARR is a public TYPE, defined inside an Oracle Package like the following:

TYPE VARCHAR2_ARR 是 VARCHAR2(50) 表；

TYPE VARCHAR2_ARR IS TABLE OF 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)  

推荐答案

VARCHAR2_ARR 类型是 PLSQL 类型，您将无法直接从 java 中对其进行接口.我建议您查看 AskTom 上的此线程 关于类似问题.

the type VARCHAR2_ARR is a PLSQL type, you won't be able to interface it directly from java. I suggest you look into this thread on AskTom regarding a similar question.

这里有一些建议:

• 创建一个可以从 java 绑定的 SQL TYPE
• 从java插入临时表并在plsql中读取

在这两种情况下，您都必须修改 PLSQL 过程或添加新的翻译过程.

In both cases you will have to either modify the PLSQL procedure or add a new translation procedure.

这篇关于java - 在oracle存储过程中传递数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！