Array processing in plsql script

Question

I want to pass an array from a shell script to my PL / SQL script as one argument, and then try to access the elements of the array in a PL / SQL script using an index. How can I achieve this?

Answer 1

Here is one way to do it. You pass a shell array as a space-separated string to a stored procedure, and then convert it to a collection — there are many ways to do this. In this example, I use string_to_table()the built-in package function apex_util.

Here is the procedure (maybe a function, it is up to you):

create or replace procedure p1(p_list in varchar2)
is
  l_array apex_application_global.vc_arr2;
begin
  -- convert p_list varchar2 sting to a collection
  l_array := apex_util.string_to_table(p_list, ' ');
  -- iterate through the collection and print each element
  for i in l_array.first..l_array.last loop
    dbms_output.put_line(l_array(i));
  end loop;
end;

Here we define and pass the shell array:

array[0] = 'a'
array[1] = 'b'
array[2] = 'c'

sqlplus testlab/testlab@nkpdb <<EOF
set serveroutput on
exec p1('${array[*]}');
EOF

Result:

SQL> exec p1('a b c');
a
b
c

PL/SQL procedure successfully completed

Answer 2

, , Oracle. Oracle collection ( varchar). .

--- using oracle defined collection for varchar2 
CREATE OR REPLACE procedure array_list_pass_proc(v_acct5  sys.odcivarchar2list)
as

begin

for i in 1..v_acct5.count -- Passing the array list to loop 
 loop
   --Printing its element
   dbms_output.put_line(v_acct5(i));

 end loop;

end;
/

:

SQL> execute array_list_pass_proc(sys.odcivarchar2list('0001','0002','0003'));
0001
0002
0003

PL/SQL procedure successfully completed.