java.sql.SQLException: invalid name pattern

[Timothy Sam]

Hi guys,

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 TYPE I 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)

Any ideas? Thanks!
[ March 10, 2008: Message edited by: Timothy Sam ]

[Jan Cumps]

At what line does the error happen?

Can you try ArrayDescriptor.createDescriptor("MY_PKG.VARCHAR2_ARR", narrowdConn); ?

Regards, Jan
[ March 10, 2008: Message edited by: Jan Cumps ]

[Timothy Sam]

Hi Jan,

Thanks but I've tried that before, it doesn't work. I even tried putting the schema and still it doesn't work...

[Sebastien David]

Hello Timothy Sam,

I would like to know if you have solved your problem. I'm currently facing the similar problem.

Thanks for your help.

[Piyush Mattoo]

Me Too.
My Procedure is:-
CREATE OR REPLACE PROCEDURE PROCESS_ARRAY(v_str_array IN Str_Array)
IS
TYPE Str_Array IS VARRAY(7) OF VARCHAR2(50);
v_array Str_Array;
BEGIN
FOR i IN v_str_array.first .. v_str_array.last LOOP
DBMS_OUTPUT.PUT_LINE('Output '||v_str_array(i));
END LOOP;
END;

The JDBC code to call the procedure is:-

proc_stmt = con.prepareCall("{ call PROCESS_ARRAY(?) }");
ArrayDescriptor descriptor =
ArrayDescriptor.createDescriptor("Str_Array", con);
ARRAY array_to_pass =
new ARRAY(descriptor, con, strArray);
proc_stmt.setArray(1, array_to_pass);

And i see the error- "java.sql.SQLException: invalid name pattern: <Schema-Name>.Str_Array"

[Tad Smith]

I was having the same problem. I had code like this:
storedProc.registerOutParameter(5, Types.ARRAY, "TEXT_ARRAY");

This also did not work:
storedProc.registerOutParameter(5, Types.ARRAY, schema + ".TEXT_ARRAY");

It turns out the variable schema had my schema name in lower case. This fixed my problem:
storedProc.registerOutParameter(5, Types.ARRAY, schema.toUpperCase() + ".TEXT_ARRAY");

I hope this helps,
-- Tad