SQL * loader Experiment 1: SQL * loader Experiment
Objective: To import txt data to a database table
Step 1: scott creates an empty table
Create table family (id number (10), name varchar (20), sal number (20), total number (30 ));
Step 2: Create a txt data file:
Vi/oracle/input.txt
1, clark
2, saber, 4100,40000
Step 3: Create an SQL * loader input control file:
Vi/oracle/input. ctl
Load data
Infile '/oracle/input.txt'
Into table family
Fields terminated ','
(Id, name, sal, total)
Step 4: Execute Import
Sqlldr scott/tiger control = '/oracle/input. ctl' log = '/oracle/input. Log'
Prompt that the import is complete:
Commit point reached-logical record count 2
Step 5: Check the table:
SQL> select * from family;
ID NAME SAL TOTAL
--------------------------------------------------
1 clark 4000 50000
2 saber 4100 40000
What is SQL * loader?
SQL * loader is a program used to import data in text files to the Oracle database.
The following is a simple example:
SQL * Loader
First, you need a control file test_main.ctl with the following content:
LOAD DATA
INFILE *
Into table test_main
Fields terminated ','
(ID, VALUE)
BEGINDATA
1, Test
Where,
The first line of load data is to tell SQL * Loader what to do? Here we load data.
The second line of INFILE * indicates where the data comes from? This is included in the control file.
The third line of into table indicates where to import data? Here we will go to the test_main table.
The fourth line of fields terminated by indicates the symbols used to separate data? Separate them with commas.
The fifth line indicates the sequence in which data is written to the column.
The sixth line of BEGINDATA is to tell SQL * Loader that data is followed.
Then start running the sqlldr Program
D: \ temp> sqlldr userid = test/test123 control = test_main.ctl
SQL * Loader: Release 10.2.0.1.0-Production on Sunday March 13 14:58:22 2011
Copyright (c) 1982,200 5, Oracle. All rights reserved.
SQL * Loader-601: The table must be empty for the INSERT option. Table TEST_MAIN Error
In SQL Plus,
SQL> truncate table test_main;
The table is truncated.
Test again later
D: \ temp> sqlldr userid = test/test123 control = test_main.ctl
SQL * Loader: Release 10.2.0.1.0-Production on Sunday March 13 14:58:56 2011
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Commit point reached-logical record count 1
SQL * Loader-350
SQL * Loader-350: the syntax error is located in row 6th, which generally occurs in the ParFile content.
Check the content syntax format in ParFile carefully. If the problem persists, contact us.