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?
Add a comment
|
2 Answers
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
4 Comments
Frank Schmitt
It might be worth noting that your second code snippet is not a shell script, but an interactive session where
$ denotes the shell prompt (it took me a couple of minutes to figure that out - esp. since $ array[0] looks suspiciously like a mis-typed $array[0], but that might just be my stupidity).Frank Schmitt
In bash, you can also define the array in a simpler fashion:
array=(a b c)
Nick Krasnov
@FrankSchmitt Shell prompt removed, Frank. Yes, there was a typo in the array indexing, thank you.
sakshi garg
@NicholasKrasnov I need to pass the arguments as command line arguments (not in a procedure) to PL/SQL script where the #(arguments) passed is not certain. How can I handle such situation and receive in PL/SQL script?
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.
1 Comment
Frank Schmitt
Please re-read the question - the OP asked about how to pass an array from a shell script to PL/SQL, not about how to create a procedure that accepts an array.