爪哇 - 在Oracle存储过程传递数组

问题描述

我有Java应用程序访问Oracle存储过程。存储过程的参数包括数组类型。我这样做如下...

  CON = this._getConnection（）;
连接narrowdConn =（连接）WSJdbcUtil.getNativeConnection（（WSJdbcConnection）CON）;。调用= CON prepareCall（{调用MY_PKG.MY_PROCEDURE（？）？}）;
ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor（VARCHAR2_ARR，narrowdConn）;
ARRAY arrayArg1 =新阵列（arrayDescriptor，CON，docNames）;
ARRAY arrayArg2 =新阵列（arrayDescriptor，CON，文档类型）;callable.setArray（1，arrayArg1）;
callable.setArray（2，arrayArg2）;callable.execute（）;
 

现在，我得到这个异​​常...

 值java.sql.SQLException：无效的名称模式：MY_PKG.VARCHAR2_ARR
 

VARCHAR2_ARR是一个公共类型，Oracle包中定义如下所示：

型VARCHAR2_ARR IS VARCHAR2表（50）;

在我的存储过程作为这样的...

 程序my_procedure的
    （V_ARR_ARG1 IN VARCHAR2_ARR，
     V_ARR_ARG2 IN VARCHAR2_ARR）
 

解决方案

类型 VARCHAR2_ARR 是一个PLSQL类型，你将不能够直接从Java接口它。我建议你​​看看<一个href=\"http://asktom.oracle.com/pls/asktom/f?p=100%3A11%3A0%3A%3A%3A%3AP11%5FQUESTION%5FID%3A712625135727\"相对=nofollow称号=传递数组到PL / SQL存储过程>这个线程关于类似问题上AskTom 。

这里有几个建议：

• 创建，你可以从Java绑定一个SQL类型


• 插入从Java临时表和PLSQL
从它读

在这两种情况下，你必须要么修改PLSQL过程或添加新的翻译过程。

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 is a public TYPE, defined inside an Oracle Package like the following:

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)

解决方案

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.

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

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

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