Oracle SQL*Loader

SQL*Loader Overview

SQL*Loader is an Oracle-supplied utility that allows you to load data from a flat file (the flat file must be formatted) into an Oracle database. SQL*Loader supports various load formats, selective loading, and multi-table loads. SQL*Loader utility (must be run at the OS level) use a control file which contains the way the data is formatted and inserted into the Oracle database. During the insert operation a discard, log and bad files are created. The log file is a record of SQL*Loader's activities during a load session. Where a row is not inserted in a table (constraint violations, not enough disk space, etc) a record is inserted in the bad file. Sometimes, in the control file there are some criteria that a record must meet before it is loaded. If the criteria is not meet the record is not inserted in the database but in the discard file. The discard file is optional. 

Invoking SQL*Loader

The SQL*Loader is invoked by running the following command:  

sqlldr scott/s control = C:\sqlloader.ctl

In this case the SQL*Loader connects to the database as scott and using the information provided in sqlloader.ctl file insert the data in the database. 

Supposing we have a text file which contains data for SCOTT.DEPT1 table. Here is the content of the file C:\DEPT.txt :

SCOTT.DEPT1 table has the following description:

For this we create the following control file C:\sqlloader.ctl   :

load data
infile 'c:\DEPT.txt'
into table DEPT1
fields terminated by "," optionally enclosed by '"' 
( DNAME, DEPTNO, LOC )

The following command will insert the data in the DEPT table:

sqlldr scott/s control = C:\sqlloader.ctl log = DEPTNO1.log discard = DEPTNO1.dis

The default behavior of SQL*Loader is to insert data in an empty table. If the table is not empty an error will occur. If we want to append data the APPEND parameter must be added in the control file. If we want to replace the old data with the new one the REPLACE parameter must be added. Here is an example using APPEND parameter:

load data
infile 'c:\DEPT.txt'

APPEND
into table DEPT1
fields terminated by "," optionally enclosed by '"' 
( DNAME, DEPTNO, LOC )

Also, the WHEN clause could be added to filter the data which will be inserted in the database:

load data
infile 'c:\DEPT.txt'
INSERT into table DEPT1
WHEN (12:13) = '10' 
( DNAME POSITION(1:10), 
DEPTNO POSITION(12:13), 
LOC POSITION(15:23))

Data transformation

Data transformation is possible during the data load. The control file must be modified to allow this. Here is an example of control file which allow data transformation:

load data
infile 'c:\DEPT.txt'
into table DEPT1
fields terminated by "," optionally enclosed by '"' 
( DNAME, 
DEPTNO, 
LOC constant "TORONTO")

Here are other examples where data is transformed at the column level:

-> using sequences:                            (...) rec_no  "SEQ_NAME.nextval", (...)

-> modifying the data from the file:   (...) hire_date POSITION(1:5) ":hire_date+1", (...)  

-> using a constant:                           (...) LOC constant "TORONTO" (...)

-> using an Oracle function:              (...) name POSITION(6:15) "upper(:name)"  (...)

Load Fixed & Variable length data records

The example which use FIELDS TERMINATED BY "," allows variable length records, because the columns are delimited by "," (could be used any other sign). Sometimes we don't have delimiters and the columns are fixed length values. In this case the control file must be like:

load data
infile 'c:\DEPT.txt'
into table DEPT1
( DNAME POSITION(1:10), 
DEPTNO POSITION(12:13), 
LOC POSITION(15:23))

and the data file must have the content like:

ACOUNTING     10 OTTAWA
MANAGEMENT 20 MONTREAL
RESEARCH         30 HALIFAX
SALES                 40 QUEBEC

More information about  this subject ( Oracle SQL*Loader ) you can get from  www.in-oracle.com

When you invoke SQL*Loader, you can specify certain parameters to establish session characteristics. Parameters can be entered in any order, optionally separated by commas. You specify values for parameters, or in some cases, you can accept the default without entering a value.

For example:
SQLLDR CONTROL=sample.ctl, LOG=sample.log, BAD=baz.bad, DATA=etc.dat
USERID=scott/tiger, ERRORS=999, LOAD=2000, DISCARD=toss.dsc,
DISCARDMAX=5

This was taken from here.

SQL*Loader provides two methods for loading data:

 - Conventional Path Load

 - Direct Path Load

A conventional path load executes SQL INSERT statements to populate tables in an Oracle database. A direct path load eliminates much of the Oracle database overhead by formatting Oracle data blocks and writing the data blocks directly to the database files. A direct load does not compete with other users for database resources, so it can usually load data at near disk speed. Considerations inherent to direct path loads, such as restrictions, security, and backup implications, are discussed in this chapter.

The tables to be loaded must already exist in the database. SQL*Loader never creates tables. It loads existing tables that either already contain data or are empty.

The following privileges are required for a load:

 - You must have INSERT privileges on the table to be loaded.

 - You must have DELETE privileges on the table to be loaded, when using the REPLACE or TRUNCATE option to empty old data from the table before loading the new data in its place.

This was taken from here.