perlquestion
Jeppe
Esteemed monks,
<p>
Is it possible to do a FORALL INSERT from Perl? I've tried writing a procedure, but binding my arrayref of arrayrefs fails. Also, from reading <a href="http://search.cpan.org/~pythian/DBD-Oracle-1.22/Oracle.pm#Returning_A_Recordset">this:</a>
<blockquote>
Returning A Recordset ^
DBD::Oracle does not currently support binding a PL/SQL table (aka array) as an IN OUT parameter to any Perl data structure. You cannot therefore call a PL/SQL function or procedure from DBI that uses a non-atomic data type as either a parameter, or a return value. However, if you are using Oracle 9.0.1 or later, you can make use of table (or pipelined) functions.
</blockquote>
To the best of my understanding, a FORALL-style INSERT requires binding a PL/SQL table - and this is impossible? Or have I misunderstood something here?
<p>
How do others insert massive amounts of data to Oracle tables? The constraints are that
<ol>
<li>The app needs the generated IDs afterwards.</li>
<li>Table must be fully available during insert.</li>
<li>There may be several processes inserting into the same table at the same time.</li>
</ol>
I'm currently attempting execute_array, and that doesn't give very much improvement - maybe 5%. Before I decide that the app needs more IO, implementing FORALL INSERT is a prerequisite. So, any help is greatly appreciated!