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, 1980🔥, 0💬
Popular Posts:
How To Select All Columns of All Rows from a Table with a SELECT statement in SQL Server? The simple...
How To Calculate DATETIME Value Differences Using the DATEDIFF() Function in SQL Server Transact-SQL...
How to run Queries with SQL Server Management Studio Express in SQL Server? 1. Launch and connect SQ...
What Are the Differences between DATE and TIMESTAMP in Oracle? The main differences between DATE and...
Is SQL Server Transact-SQL case sensitive? No. Transact-SQL is not case sensitive. Like the standard...