Passing Array of string to pl/sql function from java

Question

I am trying to pass array of values from java to PL/SQL function. I am trying as below,

String[] array = {"70" , "2" , "4" , "9" , "329" , "13" , "49" , "33"};
Connection cnn=DriverManager.getConnection(url, username, password);
 ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("VLIST", cnn);
ARRAY array_to_pass = new ARRAY(descriptor, cnn,array);
 OracleCallableStatement pstmt = (OracleCallableStatement) cnn.prepareCall("{? = call  GETSRCHLST2(?)}");
pstmt.registerOutParameter(1, Types.VARCHAR);
 pstmt.setArray(2, array_to_pass);
pstmt.execute();
String output = pstmt.getString(1);
out.println(output+"\n"+ " "+array_to_pass.length());

Here in the function call no content of array is getting passed but array_to_pass.length() is 8.

Oracle procedure:

CREATE OR REPLACE FUNCTION EDR.GETSRCHLST2(VV VLIST) RETURN VARCHAR2 IS

BN NUMBER;
STRS VARCHAR2(9000):='start';
HH varchar2(30);

BEGIN

STRS:=STRS || '- LIST COUNT: '||VV.COUNT||' - ';
BN:=VV.FIRST;

WHILE BN IS NOT NULL
LOOP

   HH:=VV(BN);
   STRS:=STRS||HH||' , ';
   BN := VV.NEXT(BN);   

END LOOP;

RETURN STRS;

EXCEPTION WHEN OTHERS THEN RETURN 'SOME ERROR'||SQLERRM;
end;

Can anyone tell me, where i am going wrong?

See that: http://stackoverflow.com/questions/5198856/pass-array-to-oracle-procedure — Kacper, Dec 18 '16 at 12:17
Hi, Can you please post your **VLIST** type declaration or DDL? — hmmftg, Dec 18 '16 at 12:57
Thank you for pointing out. I found the solution here. Changed VLIST type from VARCHAR to NVARCHAR. — techhunter, Dec 18 '16 at 13:01
@safoorasafu Ok then please provide your **VLIST** DDL and answer the question below, May be it helps someone someday, Thanks — hmmftg, Dec 18 '16 at 13:03

score 0 · Answer 1 · answered Dec 19 '16 at 13:20

Hope it may help those who is in search of same solution.

CREATE OR REPLACE
TYPE DR.VLIST AS TABLE OF VARCHAR2(50)

But the value passed from java in array will not match VARCHAR instead it expects NVARCHAR. So made a slight changes as ,

CREATE OR REPLACE
TYPE DR.VLIST AS TABLE OF NVARCHAR2(50)

it solved my the problem.

score 0 · Answer 2 · answered Aug 24 '17 at 19:04

oracle.sql.ARRAY is deprecated. Instead, you can call OracleConnection.createOracleArray(String arrayTypeName, Object elements)
Note that arrayTypeName should be the table of type and elements should be your Java array T[] where T corresponds to the type that arrayTypeName is a list of. (this can be a class implementing SQLData)

For more information, you might find my my answer on getArray() and writeArray() useful.

Passing Array of string to pl/sql function from java

2 Answers2