Expert: Suchitra Joshi - 9/25/2009

Question
HI Suchitra,

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

  
Hope this helps.

Regards

Suchitra