In any case, my own experience with this (in Solaris) has been that bulk inserts via DBI tend to be very slow -- actually, (so far as I know) you have to loop over inserts one row at a time; there is a notion of using a parameterize sql statement, like:

use DBI;

# I won't cover the "connect" part -- you'll need to read
# docs and try things...  but with $odb_handle as the
# database connection object:

my $sql = "insert into mytable (k,a,b,c) values (?,?,?,?)";
my $sql_handle = $odb_handle->prepare( $sql );

# suppose you're reading rows of space-separated fields
# from a data file:

while (<INPUT>) {
   @fields = split(/\s/);
   if ( @fields == 4 ) {
        $sql_handle->execute( @fields );
   }
}
[download]

This is fine for up to a few hundred inserts. But when you need to do a few thousand (or many hundreds at frequent intervals), you may find it goes much quicker using oracle's own "sqlload" (or is it "sqlloader"?) -- use perl to set up it's inputs and run it via a system call. I find that it not only runs many times faster, it also provides very useful error reporting (for unsuitable input and other problems that might be hard to anticipate).

As for doing selects, the DBI methods seem to perform quite well; updates may be slower, but their convenience relative to other alternatives (using oracle utilities) makes them well worthwhile.

FYI, DBD::Oracle is available on CPAN

If you're using ActivePerl, go to a command shell and type "ppm install DBD-Oracle". If not, you'll have to jump through the install hoops.

For massive (one-off) loads, pre-sort the data, alter the tables to be loaded to be NOLOGGING, use a direct path load method, re-enable LOGGING, build the indexes (massively increase SORT_AREA_SIZE for this session), take a cold backup and restart the database.

rdfield

I am not sure about those perl modules you guys mentioned, but for Pro*c, it does have equivalent for inserting, and actually also updating and deleting. I attached some examples in C:

char emp_name[50][20];
int emp_number[50];
float salary[50];
/* populate the host arrays */
EXEC SQL INSERT INTO EMP (ENAME, EMPNO, SAL)
     VALUES (:emp_name, :emp_number, :salary);
[download]

And

int emp_number[50];
float salary[50];
/* populate the host arrays */
EXEC SQL UPDATE emp SET sal = :salary
     WHERE EMPNO = :emp_number;
[download]

Even deleting with array:

int emp_number[50];
/* populate the host array */
EXEC SQL DELETE FROM emp
     WHERE empno = :emp_number;
[download]

The answers are all there, but I'll spell them out for you:

• Use DBI with DBD::Oracle.
• The fastest way to read an array is with fetchall_arrayref.
• You may be able to do fast inserts/updates with bind_param_array, but it depends on whether or not DBD::Oracle has implemented special bulk insert code or not. Try it.
• Setting RowCacheSize may help with large selects.