Archive

Archive for the ‘Oracle Utilities’ Category

SQL*Loader

October 31, 2011 Leave a comment

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.

Image Source

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 ‘\r\n'”
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 ‘\r\n'”
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           14

7 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 ‘\r\n'”
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      Employee

6 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 ‘\r\n'”
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      Employee

6 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

Categories: Oracle Utilities