Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Re: Batch loading data into Oracle - Perl to the rescue!

by t0mas (Priest)
on Jan 28, 2003 at 06:48 UTC ( #230515=note: print w/ replies, xml ) Need Help??


in reply to Batch loading data into Oracle - Perl to the rescue!

As you said. You can have the data in a separate file You don't need to concatenate anything at all.

Create a separate control file 'n1822.ctl' like:

LOAD DATA INFILE 'n1822.txt' INTO TABLE {table name} ( person_id POSITION(1:9) CHAR(9) surname POSITION(10:34) CHAR(25) given_names POSITION(35-59) CHAR(25) )

Point your loader command to that file like:

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


/brother t0mas


Comment on Re: Batch loading data into Oracle - Perl to the rescue!
Download Code
Batch loading data into Oracle - my solution
by hotyopa (Scribe) on Jan 28, 2003 at 22:52 UTC

    Thanks tomas, that's what I've done. I still wanted to automate the creation of these control files though, so this is what I did. Wrote a generic control file called control.ctl:

    LOAD DATA INFILE <table>.txt INTO TABLE <table> ( person_id POSITION(1:9) CHAR(9) surname POSITION(10:34) CHAR(25) given_names POSITION(35-59) CHAR(25) )

    Then wrote the following script to write a control file for each data file in the directory:

    #!/usr/bin/perl #configuration variables $lpath="/u04/tmp/log"; $cpath="/u04/tmp/ctl/"; $cfile="control.ctl"; $dirname="/u04/tmp/txt/"; # load the control file data open(SOURCE, "< $cpath$cfile") or die "Couldn't open $cpath for reading: $!\n"; @control = <SOURCE>; foreach $line (@control) { $ctl .= $line; } #process all files in the directory opendir(DIR, $dirname) or die "can't opendir $dirname: $!"; while (defined($file = readdir(DIR))) { if ($file =~ /.txt/) { ($table = $file) =~ s/.txt//; #print "$file -> $table \n"; #write the control file open(COUT, "> $cpath$table.ctl") or die "Couldn't open $cpath$file.ctl for writing: $!"; ($tmp = $ctl) =~ s/<table>/$table/g; print COUT $tmp; close COUT; #call SQL*Loader $cmd = "sqlldr USERID=user/id\@service ". "control=$cpath$table.ctl " . "log=$lpath$table.log " . "bad=$lpath$table.bad direct=true"; $status = system($cmd); } } closedir(DIR);

    I'm sure this code could be cleaned up a bit, but it does the job.

    *~-}hotyopa{-~*

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://230515]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (8)
As of 2014-12-26 09:52 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (171 votes), past polls