[EMAIL PROTECTED] wrote: > Hello All > > Is it possible to pass an array as a parameter into an Oracle Stored > procedure. I am using Oracle 1.18a. > > I have looked all over the place and have not found anything that > states that this is possible. If so - does anyone have an example?
Unfortunately, no. There is no *direct* way to bind a Perl array to a PL/SQL array. (As soon as I post this, I just know someone is going to say otherwise and show me how I've been doing it wrong all this time ;-) When I've needed to do this, I've done one of two things: 1) For small arrays of simple elements (e.g. array of varchar2) I've encoded them into a string on the Perl side, and then decoded that string into a PL/SQL array on the Oracle side. Messy, but relatively fast. 2) For larger arrays or arrays of composite elements (e.g. array of rowtype) I've inserted the elements as rows in a global temporary table. I then select those rows into the array "bulk collect" on the Oracle side. Flexible but slower. You could also do this one using a package array variable which you make PL/SQL calls to extend repeatedly. Once you've extended and populated the package array, you can pass it into the procedure. This might be faster than the GTT method, but I've found maintaining packages to be more work than maintaining tables. Regards, Philip
