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, 2030🔥, 0💬
Popular Posts:
What Happens If the UPDATE Subquery Returns Multiple Rows in MySQL? If a subquery is used in a UPDAT...
How To Update Multiple Rows with One UPDATE Statement in SQL Server? If the WHERE clause in an UPDAT...
How To Convert Character Strings into Numeric Values in SQL Server Transact-SQL? Sometimes you need ...
How to download and install Microsoft .NET Framework Version 2.0 in SQL Server? .NET Framework Versi...
How to download and install the scaled-down database AdventureWorksLT in SQL Server? If you want to ...