How To Load Data with SQL*Loader?
Submitted by: AdministratorLet'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
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
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
Read Online Oracle Database Job Interview Questions And Answers
Top Oracle Database Questions
☺ | How To Recover a Dropped Table in Oracle? |
☺ | How To Run SQL*Plus Commands in SQL Developer? |
☺ | What Is an Oracle User Account? |
☺ | What Is Input Buffer in SQL*Plus? |
☺ | How Oracle Handles Dead Locks? |
Top DB Oracle Categories
☺ | Oracle PL-SQL Interview Questions. |
☺ | Oracle DBA Interview Questions. |
☺ | Oracle D2K Interview Questions. |
☺ | OCI Interview Questions. |
☺ | Oracle RMAN Interview Questions. |