CODEWITHSUNDEEP
oracle-databaseshellplsql
sakshi garg
sakshi garg

Reputation: 141

Array handling in plsql script

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

Upvotes: 4

Views: 490

Answers (2)

Nick Krasnov
Nick Krasnov

Reputation: 27251

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

Here is the procedure(might be function, it's 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 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

Upvotes: 3

XING
XING

Reputation: 9886

Please see below how you can do it in Oracle. I used a Oracle defined collection(array of varchar). You can create your own collection and pass it in the similar way.

--- 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;
/

Output:

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

PL/SQL procedure successfully completed.

Upvotes: 1

Related Questions