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?

Hi, Can you please post your VLIST type declaration or DDL? — hmmftg
– hmmftg, Commented Dec 18, 2016 at 12:57
Thank you for pointing out. I found the solution here. Changed VLIST type from VARCHAR to NVARCHAR. — techhunter
– techhunter, Commented Dec 18, 2016 at 13:01
@safoorasafu Ok then please provide your VLIST DDL and answer the question below, May be it helps someone someday, Thanks — hmmftg
– hmmftg, Commented Dec 18, 2016 at 13:03

techhunter · Accepted Answer · 2016-12-19 13:20:18Z

0

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.

answered Dec 19, 2016 at 13:20

techhunter

6931 gold badge12 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Arlo · Accepted Answer · 2017-08-24 19:04:38Z

0

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.

answered Aug 24, 2017 at 19:04

Arlo

1,3512 gold badges17 silver badges26 bronze badges

Collectives™ on Stack Overflow

Passing Array of string to pl/sql function from java

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related