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
The SQL*Loader can perform several types of data loading
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
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 -- Note becareful to surround the double quotation marks with single quotation marks i.e '"' |
Example Two | load data -- Note: becareful to surround the double quotation marks with single quotation marks i.e '"' |
If your data is already formatted you can use one of the file format parameters
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 |