PERL : Bind 2D array to SQL

Shell Programming and Scripting
1

Hi,
I am in the need of doing a bulk insert via :

SQL - INSERT INTO <table> (SELECT..)
OR
PLSQL Block - FORALL i IN 1 .. count INSERT INTO <table>(arrayname(i))

I have a 2D array in my perl code which has the rows to be bulk inserted.

Is there a way to bind the 2D array to the SQL or the PLSQL block ???

I tried execute_array() but it seems it can be used for a 1D array and not for a 2D one.

Thanks.

2

Are we in Oracle? I am a bit rusty.

I remember casting java arrays as tables and doing insert into target-matching-staging-table select * from array-table.

You may have to be per-column explicit:

FORALL i IN 1 .. count insert into table_name ( column_a, column_b, ... ) values ( array[1], array[2], ... );
3

There is no support for binding two-dimensional arrays in DBD::Oracle. The best you could do is - use $sth->bind_param_array to bind an array reference and then use $sth->execute_array.

Support for passing collections or object types isn't there as well. You can pass them out from a function or procedure, but you cannot pass them in.

Depending on the details of your problem, you may be better off using SQL*Loader to load your data.

tyler_durden

4

Oracle can map files as external tables, which is another easy and high bandwidth path in.

I am not sure it is legal with sqlloader (some tools like flat files too much), but one slick direction to to popen() sqlloader and write to the pipe. The Oracle page is not specific about its pipe intolerance!

5

Thanks to all for your replies. It helps.