Don't ask to ask, just ask | |
PerlMonks |
Batch loading data into Oracle - Perl to the rescue!by hotyopa (Scribe) |
on Jan 28, 2003 at 05:44 UTC ( [id://230512]=perlquestion: print w/replies, xml ) | Need Help?? |
hotyopa has asked for the wisdom of the Perl Monks concerning the following question: 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{-~*
Back to
Seekers of Perl Wisdom
|
|