We help IT Professionals succeed at work.

Pass Array to oracle stored procedure (owa_util.ident_arr)

Ravindra76
Ravindra76 asked
on

JDk   : 1.3

Oracle Drivers : classes12.zip ( thin drivers Jdbc Type 4)

How to pass an array of strings to to the stored prcocedure with the PL/SQL data type owa_util.ident_arr ?


advacned thanks


Comment
Watch Question

CERTIFIED EXPERT

Commented:
In your stored procedure :

PROCEDURE YourStoredProcedure(yourArray IN owa.vc_arr)
IS
BEGIN
    FOR idx IN 1..yourArray.COUNT LOOP
        IF (yourArray(idx) IS NOT NULL) THEN
        BEGIN
....

In your Java code you have to "collect" yourArray in this way :

private void memberParams(StringBuffer params) {    
 for (int i = 0; i < yourArray.size(); i++) {
        params.append("&pServiceID=" + "elemnt(i) of yourArray");
 }
} //end of adMemberParams

I hope it will help you.

Best Regards,
Valeri
CERTIFIED EXPERT

Commented:
Ooops sorry :-)

params.append("&yourArray=" + "elemnt(i) of yourArray");
instead of
params.append("&pServiceID=" + "elemnt(i) of yourArray");

Valeri

Author

Commented:
That's for the case of Web.

Won't work for noraml case..........

CERTIFIED EXPERT

Commented:
What does it means "That's for the case of Web." ?

Best Regards,
Valeri

Author

Commented:
Ok. Finally i got the solution....... my self....

String s_arr[] = {"ravi","ramesh"};
ArrayDescriptor in_arr = ArrayDescriptor.createDescriptor("T_ARRAY", conn);              
ARRAY arr = new ARRAY(in_arr,conn,s_arr);
cs = (OracleCallableStatement)conn.prepareCall("begin p_ac_test(:1,:2,:3); end;" ) ;
cs.setARRAY(1,arr);
...

where T_ARRAY is the data type in PL/SQL

create or replace TYPE t_array IS varray(200) of varchar2(100)
/


CERTIFIED EXPERT
Commented:
M ?! :-)))
Cheers!!!

Author

Commented:
Any way deleting the question does not look good