i would like to pass a arraylist in a stored procedure with other parameters. I am unable to achieve this.
proc_name(<array list>, para1, para2, para3)
Kindly let me know how to acheive this.
ops$tkyte%ORA11GR2> create or replace package my_types
2 as
3 type array is table of number index by binary_integer;
4 end;
5 /
Package created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace procedure proc1( p1 in my_types.array, p2 number, p3 date )
2 as
3 begin
4 dbms_output.put_line( 'p2 = ' || p2 || ', p3 = ' || p3 );
5 for i in 1 .. p1.count
6 loop
7 dbms_output.put_line( 'p1('||i||') = ' || p1(i) );
8 end loop;
9 end;
10 /
Procedure created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace procedure proc2
2 as
3 l_data my_types.array;
4 begin
5 select user_id bulk collect into l_data
6 from all_users
7 where rownum <= 10;
8
9 proc1( l_data, 42, sysdate );
10 end;
11 /
Procedure created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec proc2;
p2 = 42, p3 = 01-AUG-12
p1(1) = 0
p1(2) = 5
p1(3) = 9
p1(4) = 21
p1(5) = 30
p1(6) = 31
p1(7) = 32
p1(8) = 2147483638
p1(9) = 42
p1(10) = 43
PL/SQL procedure successfully completed.