Load Data with SQL*Loader in Oracle

Q

How To Load Data with SQL*Loader in Oracle?

✍: FYIcenter.com

A

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\text\student.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

You can create a control file at \oraclexe\test\student.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 \oraclexe\app\oracle\product\10.2.0\server\BIN
>sqlldr userid=hr/fyicenter, 
  control=\oraclexe\test\student.ctl, 
  data=\oraclexe\test\student.txt, 
  log=\oraclexe\test\student.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\test\student.log.

 

What Is an External Table in Oracle

What Is a SQL*Loader Control File in Oracle

Loading and Exporting Data in Oracle

⇑⇑ Oracle Database Tutorials

2016-11-27, 1980🔥, 0💬