Load Data with SQL*Loader in Oracle
How To Load Data with SQL*Loader in Oracle?
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.
2016-11-27, 847👍, 0💬
Where to find answers to frequently asked questions on Installing Sample Scripts and Databases in SQ...
How To Use "IF ... ELSE IF ..." Statement Structures in SQL Server Transact-SQL? "IF ... ELSE IF ......
What is dba.FYIcenter.com Website about? dba.FYIcenter.com is a Website for DBAs (database administr...
Where to find answers to frequently asked questions on Downloading and Installing SQL Server 2005 Ex...
Where to find answers to frequently asked questions on Conditional Statements and Loops in SQL Serve...