in reply to Oracle + Perl + bulk operation?

You can adjust the number of rows that DBI will fetch at one time. I don't think there's an equivalent for inserting though. You could also look at the experimental Oracle-OCI module which provides access to Oracle's direct API.

Replies are listed 'Best First'.
Re: Re: Oracle + Perl + bulk operation?
by pg (Canon) on Dec 17, 2002 at 06:04 UTC
    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]
[reply]
[d/l]
[select]
      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.
[reply]

In Section Seekers of Perl Wisdom