SQL*Loader is a tool used by DBAs and developers to populate Oracle tables with data from flat files. It is possible to selectivly exclude records and transform records while they’re loaded. The load produces a log file, bad files and discard files. The control file specifies the behaviour of the SQL*Loader.
A control file contains three sections:
1st section: The first section contains global options: bindsize, rows, records to skip etc. Also, the INFILE clause determines the location of the input data.
2st section: The second section contains one or more INTO TABLE blocks.
3st section: The (optional) third section contains input data.
INFILE
The INFILE statement specifies the file to be loaded. It also indicates whether the records are fixed length, variable length or stream format.
INTO TABLE
The into table statement defines the relationship between the fields in an Oracle table and the records in the datafile to be loaded.
APPEND
One of the options to be used if the table is non empty.
INSERT
FIELDS TERMINIATED BY
If the fields of the data that is being loaded are not fixed width, it must be specified how they’re seperated.
WHEN
When is used to selectively load data (according to a criteria stated after the when) into the table to be loaded.
BEGINDATA
If the data to be loaded is in the control file itself, the start of the data is marked with a BEGINDATA statement.
SQL*Loader in action consist of several additional items. If, in the course of performing data loads, SQL*Loader encounters records it cannot load, the record is rejected and the SQL*Loader puts it in special file called bad file. Additionally, SQL*Loader gives the user options to reject data based on special criteria. These criteria are defined in the control file as a part of the when clause. Is the SQL*Loader encounters a record that fails a specified when clause, the record is placed in a special file called discard file.
Examples:
A. Insert records into an empty table.
1.Create an empty table:
create table loader_1 (
load_time date,
field_1 varchar2(10),
field_2 varchar2(10)
) ;
2. Create Control file:
load data
infile ‘loader_1.dat’ “str ‘rn'”
insert
into table loader_1
(
load_time sysdate,
field_2 position( 1:10),
field_1 position(11:15)
)
Data file: loader_1.dat
0123456789abcde
test line next
another test line
3. Start SQL*Loader with sqlldr and load data file
C:>sqlldr control=loader_1.ctl userid=test/test
SQL*Loader: Release 10.1.0.2.0 – Production on Mon Oct 31 10:50:48 2011
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Commit point reached – logical record count 2
Commit point reached – logical record count 3
4. Verify the table
SQL> select * from loader_1;
LOAD_TIME FIELD_1 FIELD_2
——— ———- ———-
31-OCT-11 abcde 0123456789
31-OCT-11 next test line
31-OCT-11 st li another te
B. Insert records that are seperated by a comma.
1.Create a table:
create table loader_2 (
field_1 varchar2(10),
field_2 number
);
2. Create Control file:
load data
infile ‘loader_2.dat’ “str ‘rn'”
insert
into table loader_2
fields terminated by ‘,’
(
field_1 char,
field_2 integer external
)
‘integer external’ for field_2 means, that the numbers in the dat file are human readable rather than “real” bytes
Data file: loader_2.dat
one,1
two,2
three, 3
fifty,50
forty, 40
eighty-eight,88
one hundred,100
hundred,100
fourteen,14
3. Start SQL*Loader with sqlldr and load data file
C:>sqlldr control=loader_2.ctl userid=test/test
SQL*Loader: Release 10.1.0.2.0 – Production on Mon Oct 31 11:19:18 2011
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Commit point reached – logical record count 8
Commit point reached – logical record count 9
4. Verify the table:
SQL> select * from loader_2;
FIELD_1 FIELD_2
———- ———-
one 1
two 2
three 3
fifty 50
forty 40
hundred 100
fourteen 147 rows selected.
Two records that could not be loaded are found in the bad file.
Bad file: loader_2.bad
eighty-eight,88
one hundred,100
C. Selectively insert records according to a criteria.
1.Create a table:
create table loader_3 (
field_1 varchar2(10),
field_2 varchar2(10)
);
2. Create Control file:
Suppose only those records whose second field equals Employee is to be loaded, then
load data
infile ‘loader_3.dat’ “str ‘rn'”
discardfile ‘loader_3.dsc’
insert
into table loader_3
when field_2 = ‘Employee’
fields terminated by ‘;’
(
field_1 char,
field_2 char
)
Data file: loader_3.dat
Name1;Employee;
Name2;Employee;
Name3;Employee;
Name4;Employee;
Name5;Admin;
Name6;Employee;
Name7;Employee;
Name8;Admin;
3. Start SQL*Loader with sqlldr and load data file
C:>sqlldr control=loader_3.ctl userid=test/test
SQL*Loader: Release 10.1.0.2.0 – Production on Mon Oct 31 11:36:58 2011
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Commit point reached – logical record count 7
4. Verify the table:
SQL> select * from loader_3;
FIELD_1 FIELD_2
———- ———-
Name1 Employee
Name2 Employee
Name3 Employee
Name4 Employee
Name6 Employee
Name7 Employee6 rows selected.
Records that didn’t meet the criteria are found in the discard file:
Discard file: loader_3.dsc
Name5;Admin;
Name8;Admin;
D. Selectively insert records into two different tables.
1.Create two tables:
create table loader_4_1 (
field_1 varchar2(10),
field_2 varchar2(10)
);create table loader_4_2 (
field_1 varchar2(10),
field_2 varchar2(10)
);
2. Create Control file:
load data
infile ‘loader_4.dat’ “str ‘rn'”
discardfile ‘loader_4.dsc’
insert
into table loader_4_1
when field_2 = ‘Employee’
(
field_1 position(1) char(5),
field_2 position(7) char(8)
)
into table loader_4_2
when field_2 = ‘Admin’
(
field_1 position(1) char(5),
field_2 position(7) char(5)
)
Data file: loader_4.dat
Name1 Employee
Name2 Employee
Name3 Employee
Name4 Employee
Name5 Admin
Name6 Employee
Name7 Employee
Name8 Admin
3. Start SQL*Loader with sqlldr and load data file
C:>sqlldr control=loader_4.ctl userid=test/test
SQL*Loader: Release 10.1.0.2.0 – Production on Mon Oct 31 11:56:27 2011
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Commit point reached – logical record count 7
Commit point reached – logical record count 8
4. Verify tables:
SQL> select * from loader_4_1;
FIELD_1 FIELD_2
———- ———-
Name1 Employee
Name2 Employee
Name3 Employee
Name4 Employee
Name6 Employee
Name7 Employee6 rows selected.
SQL> select * from loader_4_2;
FIELD_1 FIELD_2
———- ———-
Name5 Admin
Name8 Admin
Reference Links:
http://orafaq.com/wiki/SQL*Loader
http://www.adp-gmbh.ch/ora/tools/sql_loader/index.html
http://oreilly.com/catalog/orsqlloader/chapter/ch01.html