Using SQL-Loader

There are four stages to loading data using SQL-Loader:

1. Create a data file. The data file contains the data that you wish to load. There is one record per line and each attribute value is separated by a comma.
2. Create the relation for the data.
3. Create a control file. The control file tells Oracle how to load data from the data file.
4. Run SQL-Loader. SQL-Loader reads the control file and loads the data. A log file is produced that describes what happened and describes any errors that may have occurred.

Ex:
1. Create a data file

The data file is a text file that contains the data. Create a text file by running the text editor and typing the following data:

10, Finance, MA
11, Arts, NJ
12, Science, WA
13, Research, IL

Save the file as sample.dat

2. Create the relation

Create a relation testtab in your Oracle account using the following command:

CREATE TABLE testtab
(deptno NUMBER(3) NOT NULL,
deptname VARCHAR2(15),
deptloc VARCHAR2(15));

The data in sample.dat will be loaded into the relation testtab using SQL-Loader. The relation testtab must exist in the database before SQL-Loader can load data into it.

3. Create a control file

The control file describes the structure of the data and indicates the relation into which the data should be loaded. Create a text file containing the following:

LOAD DATA
INFILE 'E:\sample.dat'
INTO TABLE testtab
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(deptno, deptname, deptloc)

Save the file as sample.ctl

4. Execute SQL-Loader

The command to execute the SQL-Loader is:

sqlldr userid=tester@test control=E:\sample.ctl log=E:\sample.log