A job that I know is crying out for a perl solution, but its been a while and I can't quite get my head around it:

I'm using (or at least about to use) Oracle's 'sqlldr' utility to batch load some data into a database. I have about 128 files (some having up to 1,000,000 rows) with names like 

'n1822.txt','n2327.txt'
which are to be loaded into tables with almost the same names - 
'n1822','n2327'
(ie without the .txt)

Now, the sqlldr command (I'm on a Linux box) uses a control file to tell it how the data is formatted, which can be either a seperate text file, or embedded in the data file as a header (as below). This is my control file: 

LOAD DATA
INFILE *  -- tells sqlldr the data is in this file
INTO TABLE {table name}
(
  person_id		POSITION(1:9)		CHAR(9)
  surname		POSITION(10:34)		CHAR(25)
  given_names		POSITION(35-59)		CHAR(25)
)
BEGINDATA
{data begins here}
and we call sqlldr from the command line as follows:

# sqlldr USERID=scott/tiger control='n1822.txt' log='log.txt' bad='bad.txt' direct=true

What I imagine I need is two scripts - one which prepends the control info onto the beginning every .txt file in my import directory, inserting the {table name} part, and another which calls sqlldr for each file in the directory.

I would love some suggestions about the key ingredients of these scripts. I'm particularly interested to know whether there is an efficient way to concatenate the small control file on to the top of the large data file.

Thanks!

*~-}hotyopa{-~*

In reply to Batch loading data into Oracle - Perl to the rescue! by hotyopa

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.