Oracle Database Interview Question:

How To Load Data with SQL*Loader?

Submitted by: Administrator

Let's say you have a table defined as:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
first_name VARCHAR(80) NOT NULL,
last_name VARCHAR(80) NOT NULL,
birth_date DATE NOT NULL,
social_number VARCHAR(80) UNIQUE NOT NULL);

There is an input data file stored at oraclexe extstudent.txt with the following records:

1,Steven,King,17-JUN-77,515.123.4567
2,Neena,Kochhar,21-SEP-79,515.123.4568
3,Lex,De Haan,13-JAN-83,515.123.4569
4,Alexander,Hunold,03-JAN-80,590.423.4567
5,Bruce,Ernst,21-MAY-81,590.423.4568
6,David,Austin,25-JUN-87,590.423.4569
7,Valli,Pataballa,05-FEB-88,590.423.4560
8,Diana,Lorentz,07-FEB-89,590.423.5567
9,Nancy,Greenberg,17-AUG-84,515.124.4569
10,Daniel,Faviet,16-AUG-84,515.124.4169

Submitted by: Administrator

You can create a control file at oraclexe eststudent.ctl as:

LOAD DATA
APPEND INTO TABLE STUDENT
FIELDS TERMINATED BY ','
(id, first_name, last_name, birth_date, social_number)

When you are ready to load data, you can run the SQL*Loader with the "sqlldr" command:

>cd oraclexeapporacleproduct10.2.0serverBIN
>sqlldr userid=hr/globalguideline,
control=oraclexe eststudent.ctl,
data=oraclexe eststudent.txt,
log=oraclexe eststudent.log

SQL*Loader: Release 10.2.0.1.0 -
Commit point reached - logical record count 10

To see details of the loading process, you should check the log file oraclexe eststudent.log.
Submitted by: Administrator