Passing an Array to Oracle using DBI

--=_alternative 004D9640862571F5_=
Content-Type: text/plain;
charset=us-ascii
Content-Transfer-Encoding: 7bit

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?

Thanks in advance

Robert Gorrebeeck
Concentra Online Services
972.725.6956
Robert_Gorrebeeck@concentra.com
****** CONFIDENTIALITY NOTICE ******
NOTICE: This e-mail message and all attachments transmitted with it may contain legally privileged and confidential information intended solely for the use of the addressee. If the reader of this message is not the intended recipient, you are hereby notified that any reading, dissemination, distribution, copying, or other use of this message or its attachments is strictly prohibited. If you have received this message in error, please notify the sender immediately and delete this message from your system. Thank you.

--=_alternative 004D9640862571F5_=--

RE: Passing an Array to Oracle using DBI

Robert_Gorrebeeck@concentra.com 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