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.doc
· 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

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

SQL*Loader control file

This is a simple text file which has specific details about the data load job, such has location on the data
file.There are many option, i have listed a number below but its best to read the Oracle documentation
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 explicity 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