Is there any way by which we can store a list of file names in an array variable in unix and pass it to pl/sql procedure as an 'IN' parameter, so that we can loop through the array and access the file names inside the pl/sql procedure.
If yes, please provide the syntax.
Thanks in advance. Prompt response would be highly appreciated due to urgency in the requirement.
Siddharth
Answer Hi Siddharth,
According to my knowledge we cannot pass unix array to PL/SQL procedure.
But here is an alternative -
1) Create a temporary table 'unixfiles' with column 'filename'.
2) Loop through the unix filenames and insert them in this table ($user is database user and $passwd is database password).
eg.
for filename in `find . -name '*'`
do
sqlplus -s $user/$passwd << _EOF
insert into unixfiles values ($filename);
commit;
_EOF
done
3) Now in the oracle procedure first create an associative array and assign these filenames, so that you can use this collection in your procedure further.
eg.
declare
type t_file is table of varchar2(50)
index by pls_integer;
l_filename t_file;
begin
select *
bulk collect into l_filename
from unixfiles;
for idx in l_filename.first .. l_filename.last
loop
dbms_output.put_line(l_filename(idx));
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
rollback;
end ;
/
