SQL Loader | Oracle DBA Tutorial pdf

SQL Loader

Using SQL Loader, you can load data from text file to Oracle tables.
The components you need to run SQL Loader are:
1. Control file – specifies how to load data in Oracle
2. Data file – the data in text file which will be loaded
3. Discard file – data that is discarded by SQL Loader because of not
matching load condition
4. Bad file – data that SQL Loader could not load because of error
5. Log file – synopsis of loading operation
If you specify DIRECT=Y option, SQL Loader will bypass buffer and save data directly into data blocks in the disk. It makes data loading very fast however, this option does not enforce constraints, does not fire insert triggers, does not allow SQL functions in control file and locks entire table during loading.
You can run SQL Loader from OS command prompt as:
SQLLDR user/password@db CONTROL=myfile.ctl
A sample control file is shown below (assuming input data file is | delimited).
LOAD DATA
INFILE 'F:\DUMP\CUSTOMER.TXT'
BADFILE 'F:\DUMP\CUSTOMER.BAD'
DISCARDFILE 'F:\DUMP\CUSTOMER.DSC'
APPEND
INTO TABLE CUSTOMER
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
CUSTNO                     DECIMAL EXTERNAL "sq_cust_no.nextval",
CUSTNAME               CHAR,
SEX                             CHAR,
ADDRESS                   CHAR,
CITY                            CHAR,
STATE                         CHAR,
COUNTRY                 CHAR,
POSTCODE               CHAR,
PHONE                      CHAR,
EMAIL                       CHAR,
NOTE                         CHAR,
DATESTAMP             DATE "YYYY-MM-DD HH24:MI:SS”
)
Note for NUMBER columns you specify DECIMAL EXTERNAL and for VARCHAR2 columns you specify CHAR in SQL Loader control file.