The things you want to do are directly supported by the DBI module, used in combination with DBD::Oracle; the former is available from CPAN, the latter may need to be gotten from your oracle distribution, or the oracle web site (I actually don't know here to get it, personally).

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]
(Of course, you may need to add some better validation and some error checking...)

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.

In reply to Re: Oracle + Perl + bulk operation? by graff
in thread Oracle + Perl + bulk operation? by pg

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.