...in some cases we load a file with millions of rows and that still takes sqlldr a moderate amount of time ~ 1-3 hours depending on how much text we insert into clob fields.

The loading of that quantity into oracle is not going to take any less time by doing it any other way. I can appreciate a desire to speed things up, and it does seem likely that if you avoid writing that quantity of data to disk, but instead pipe it directly sqlldr, you will save the amount of time that it takes to write to and read back from the disk.

In other words, by streaming data directly into sqlldr, neither the data creation nor the database loading go any faster -- you just eliminate the intermediate delay of waiting till all the data is on disk before loading it.

That said, you might want to use a "tee" in your pipeline: have the data go to a file while it's also going directly to sqlldr, so you have something to look at and work from if anything goes awry.

You are certainly right that using DBI "insert" statements would be orders of magnitude slower.

In reply to Re^3: using stdin with sqlldr by graff
in thread using stdin with sqlldr by chuckd

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.