Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
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
Replies are listed 'Best First'.
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 taking refuge in the Monastery: (11)
As of 2015-07-28 08:47 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (254 votes), past polls