Example of SQL*Loader

SQL*Loader is an Oracle tool to load text files into a database, therefore you might need to use it sometimes. It's very easy to use and the following example is almost self-explanatory.

First, you have a table like this in your datadabase:

SQL> describe my_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SOMEDATE                                           DATE
 FIELD1                                             VARCHAR2(256)
 FIELD2                                             VARCHAR2(256)

And you want to load data like this from plain text files:

08/10/2011|00:09:52|Some data|THRHTRDHF
08/15/2011|00:10:57|More data|NLHIJKUYJ
08/22/2011|00:11:00|This data|XEFGRGCG
08/26/2011|00:12:27|A lot of data|TVGT

First you have to write a small control file for sqlldr, something like this:

LOAD DATA
APPEND
INTO TABLE my_table
FIELDS TERMINATED BY "|" TRAILING NULLCOLS
(
 SOMEDATE   "to_date(:SOMEDATE, 'MM/DD/YYYY HH24:MI:SS')",
 FIELD1     ,
 FIELD2    
)

Here you say that you want to append some data in my_table, separated by |, and converts the date found in text files with to_date function. But wait! You might have noticed that there are four fields per line and just three fields in the table; you need to do some basic formatting to the text files before loading them with sqlldr.

With this small script the formatting and loading of files is automated, and you just need to run it with the text file name as a parameter in order to get loaded the file:

conn="myuser/mypass@orcl"

cd /mydir/myloader
mknod loader.pipe p
cat $1|sed 's/|/ /' > loader.pipe &
sqlldr $conn skip=0 errors=999999999 data=loader.pipe control=loader.ctl log=loader.log bad=loader.bad
wait
rm loader.pipe

This way you place the loader script in /mydir/myloader, and the script creates a named pipe to feed sqlldr with sed's formatted data like this:

08/10/2011 00:09:52|Some data|THRHTRDHF
08/15/2011 00:10:57|More data|NLHIJKUYJ
08/22/2011 00:11:00|This data|XEFGRGCG
08/26/2011 00:12:27|A lot of data|TVGT

With a script you can uncompress files on the fly or do more complex transformations to text files before loading them, and in this case you're instructing sqlldr to load as many records as it can ignoring errors (errors=999999999) and to write bad records to a file (bad=loader.bad).

Finally, running this script looks like this:

oracle@myserver:~$ /mydir/myloader/loader /otherdir/file.txt

SQL*Loader: Release 11.1.0.7.0 - Production on Mon Sep 19 11:33:03 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Commit point reached - logical record count 4

More information:

SQL*Loader FAQ
SQL*Loader Command-Line Reference