java - passing array in oracle stored procedure

Question

Hi,

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)

Answer 1

Hi coder,

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.