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, 2257🔥, 0💬
Popular Posts:
How To Update Multiple Rows with One UPDATE Statement in SQL Server? If the WHERE clause in an UPDAT...
How To Locate and Take Substrings with CHARINDEX() and SUBSTRING() Functions in SQL Server Transact-...
How to download and install the scaled-down database AdventureWorksLT in SQL Server? If you want to ...
How to download and install Microsoft SQL Server Management Studio Express in SQL Server? Microsoft ...
Collections: Interview Questions MySQL Tutorials MySQL Functions Oracle Tutorials SQL Server Tutoria...