passing array as IN and OUT parameter

[anandkl]

Hi Friends,
I have a stored proc which has 5 parameters, 3 IN parameters and 3 as OUT parameter. One of the IN parameter is and array and 2 of the OUT parameter is also an array.
Can u please help, how i can execute this procedure by passing the array as IN and OUT paramter.

My stored proc is as follows

CREATE OR REPLACE PROCEDURE test
(
p_ctr_arr IN listings_package.CharArrayTyp,
p_listings IN INTEGER,
p_mktplace IN VARCHAR2,
p_kount_array OUT listings_package.CounterTabTyp,
p_eday_array OUT listings_package.EbayTabTyp,
p_error_code OUT NUMBER )
IS

i am using this pl/sql to execute the above stored proc...but it does not return any dbms_output.put_line messages in the main stored proc

DECLARE
p_ctr_arr listings_package.CharArrayTyp;
p_kount listings_package.CounterTabTyp;
p_eday_array listings_package.EbayTabTyp;
p_err number;
BEGIN

for k in 1..p_ctr_arr.count loop
p_ctr_arr(1) :='1234';
p_ctr_arr(1) :='2484761451';
multi_read_listings_v2 (p_ctr_arr,200,'eday',p_kount,p_eday array,p_err);
dbms_output.put_line(p_ctr_arr(k));
dbms_output.put_line(p_err);
end loop;
end;

PL/SQL procedure successfully completed.

[LKBrwn_DBA]

Do you think maybe because p_ctr_arr.count is = 0?

[anandkl]

If p_ctr_arr= 0 , then i would get NO_DATA_FOUND error.

Can u please let me know if i writtne the below pl/sql...in the correct way

DECLARE
p_ctr_arr listings_package.CharArrayTyp;
p_kount listings_package.CounterTabTyp;
p_eday_array listings_package.EbayTabTyp;
p_err number;
BEGIN

for k in 1..p_ctr_arr.count loop
p_ctr_arr(1) :='1234';
p_ctr_arr(1) :='2484761451';
multi_read_listings_v2 (p_ctr_arr,200,'eday',p_kount,p_eday array,p_err);
dbms_output.put_line(p_ctr_arr(k));
dbms_output.put_line(p_err);
end loop;
end;


regards
anandkl

[LKBrwn_DBA]

Then, where are you setting the value for this statement?:

for k in 1..p_ctr_arr.count loop

[anandkl]

Inside the loop..
p_ctr_arr(1) :='1234';
p_ctr_arr(1) :='2484761451';

regards
anandkl

[LKBrwn_DBA]

Originally posted by anandkl
Inside the loop..
p_ctr_arr(1) :='1234';
p_ctr_arr(1) :='2484761451';

Wrong! For the loop to execute as written in your code, p_ctr_arr.count must have already a value > 0 and the p_ctr_arr already be loaded with that number of items.

[gandolf989]

Did you do this in sqlplus?

set serveroutput on