Collections:
Load Data with SQL*Loader in Oracle
How To Load Data with SQL*Loader in Oracle?
✍: FYIcenter.com
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
2016-11-27, 2534🔥, 0💬
Popular Posts:
How To Create a Stored Program Unit in Oracle? If you want to create a stored program unit, you can ...
How To Present a Past Time in Hours, Minutes and Seconds in MySQL? If you want show an article was p...
How To Install Oracle Database 10g XE in Oracle? To install 10g universal edition, double click, Ora...
How to set database to be READ_ONLY in SQL Server? Databases in SQL Server have two update options: ...
What Is the Difference Between GETDATE() and GETUTCDATE() in SQL Server Transact-SQL? The difference...