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 this:
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.
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?
How do others insert massive amounts of data to Oracle tables? The constraints are that
- The app needs the generated IDs afterwards.
- Table must be fully available during insert.
- There may be several processes inserting into the same table at the same time.
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!