Oracle SQL*Loader

SQL*Loader is a tool that is used to load data into a database, it is very powerful and has the following capabilities

use it to transform data before it's loaded into the database
load data from multiple sources i.e. disk, tape and named pipes
load data across a network
selectively load from the input file based on conditions
load part or all of a table
perform simultaneous loads
auto the whole process using the Oracle scheduler
load complex object-relational data

The SQL*Loader can perform several types of data loading

conventional - reads multiple rows at the same time into a bind array then inserts all at once and then commits them, basically using insert statements
direct-path - does not use insert statements, basically loads the data blocks above the high water mark then adjusts it after it has finished.
external - the new external tables feature uses SQL*Loader to access external data as if it were part of the database tables.

direct-path loading is much faster than conventional loading as it bypasses the Oracle SQL mechanism, however there are few options available when using this option. Direct-load has the following criteria

loads data directly into the data block en mass by passing the buffer cache and redo and undo logs
indexes are rebuild after loading
foreign keys and check constraints are disabled during loading (automatically disabled then re-enabled)
primary, unique keys and null keys are enabled during loading
no activity is allowed on the table as it is locked
triggers do not fire
clusters are not supported
foreign keys are disabled when loading
loading parent and child tables together is not supported
loading varray or bfile columns is not supported
you cannot apply SQL functions during loading

Use the below as a guide

small tables	conventional loading
large tables	direct loading

There are two steps when using the SQL*Loader, firstly select the data that you want to load and secondly create a SLQ*Loader control file

SQL*Loader control file

There are many option that the controlfile can have, I have listed the most common ones below but its best to read the Oracle documentation to get a full listing of all the options available.

LOAD DATA	this means load data from the infile
INFILE	location of the data, this could be a full pathname file or * (data in in the controlfile)
BADFILE	if any records are rejected due to data formatting, the record will be written to the bad file
DISCARDFILE	records that have been rejected because they didn't meet the record selection criteria you specified in the control file
INSERT	Action taken with the data in this case INSERT but you can also have TRUNCATE, REPLACE or APPEND options
OPTIONS	you can specify when type of data loading direct-path or conventional
FIELDS	fields to include in data loading
BEGINDATA	start of the data which is in the control file (see INFILE)
Examples
Example One	load data infile * badfile test.bad discardfile test.dsc insert into table <tablename> fields terminated by ',' optionally enclosed by '"' ( column1 position (1:2) char, column2 position (3:9) integer external, column3 position (10:15) integer external, column4 position (16:16) char ) begindata AY3456789111111Y -- Note becareful to surround the double quotation marks with single quotation marks i.e '"' -- Note used insert so the table must be empty first (can also use truncate, replace or append)
Example Two	load data infile * badfile test.bad discardfile test.dsc truncate into table <tablename> fields terminated by ',' optionally enclosed by '"' -- This is a comment ( FNAME char, LNAME char, DOB DATE "DD-MM-YYYY" ) begindata "Paul",Valle,"01-01-1900" Lorraine,"Valle",02-01-1900" -- Note: becareful to surround the double quotation marks with single quotation marks i.e '"' -- Note: Used truncate to clearout the table first (can also use truncate, replace or append) -- Note: char by default uses 255 characters but you optionally specify i.e char(100) -- Note: I have surrounded some data with double quotes which is picked up by the optionally enclosed by

If your data is already formatted you can use one of the file format parameters

stream record format - uses the record terminator to indicate the end of the record, this is the slowest of the methods
variable record format - you explicitly specify the length at the beginning of the record
fixed record format - you specify that all records are a specific fixed size

Invoking SQL*Loader

You can either use a parameter file or specify all the parameters on the command line

using command line options

sqlldr userid=vallep control=test.ctl data=test.dat log=log.txt errors=0 direct=true

using control file

sqlldr parfile=sales_load.par

Note: the parameter will contain the command line options