In Oracle : SQL Loader in Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Maintenance Tasks

-> SQL Loader in Oracle

 

 SQL Loader in Oracle

 

 

Here are the main topics for this article:

SQL*Loader Overview

 

SQL Loader

 


SQL*Loader is an Oracle-supplied utility that allows you to load data from a flat file (the flat file must be formatted) into an Oracle database. SQL*Loader supports various load formats, selective loading, and multi-table loads. SQL*Loader utility (must be run at the OS level) use a control file which contains the way the data is formatted and inserted into the Oracle database. During the insert operation a discard, log and bad files are created. The log file is a record of SQL*Loader's activities during a load session. Where a row is not inserted in a table (constraint violations, not enough disk space, etc) a record is inserted in the bad file. Sometimes, in the control file there are some criteria that a record must meet before it is loaded. If the criteria is not meet the record is not inserted in the database but in the discard file. The discard file is optional. 

 

 

Invoking SQL*Loader

 

The SQL*Loader is invoked by running the following command:  

 

sqlldr scott/s control = C:\sqlloader.ctl

 

In this case the SQL*Loader connects to the database as scott and using the information provided in sqlloader.ctl file insert the data in the database. 

 

Supposing we have a text file which contains data for SCOTT.DEPT1 table. Here is the content of the file C:\DEPT.txt :

SQL Loader

 


SCOTT.DEPT1 table has the following description:

 

SQL Loader  

 

For this we create the following control file C:\sqlloader.ctl   :

 

load data
infile 'c:\DEPT.txt'
into table DEPT1
fields terminated by "," optionally enclosed by '"' 
( DNAME, DEPTNO, LOC )

 

The following command will insert the data in the DEPT table:

sqlldr scott/s control = C:\sqlloader.ctl log = DEPTNO1.log discard = DEPTNO1.dis

 

SQL Loader 

 

The default behavior of SQL*Loader is to insert data in an empty table. If the table is not empty an error will occur. If we want to append data the APPEND parameter must be added in the control file. If we want to replace the old data with the new one the REPLACE parameter must be added. Here is an example using APPEND parameter:

 

load data
infile 'c:\DEPT.txt'

APPEND
into table DEPT1
fields terminated by "," optionally enclosed by '"' 
( DNAME, DEPTNO, LOC )

 

Also, the WHEN clause could be added to filter the data which will be inserted in the database:

 

load data
infile 'c:\DEPT.txt'
INSERT into table DEPT1
WHEN (12:13) = '10' 
( DNAME POSITION(1:10), 
DEPTNO POSITION(12:13), 
LOC POSITION(15:23))

 

 

Data transformation

 

Data transformation is possible during the data load. The control file must be modified to allow this. Here is an example of control file which allow data transformation:

 

load data
infile 'c:\DEPT.txt'
into table DEPT1
fields terminated by "," optionally enclosed by '"' 
( DNAME, 
DEPTNO, 
LOC constant "TORONTO")

 

Here are other examples where data is transformed at the column level:

-> using sequences:                            (...) rec_no  "SEQ_NAME.nextval", (...)

-> modifying the data from the file:   (...) hire_date POSITION(1:5) ":hire_date+1", (...)  

-> using a constant:                           (...) LOC constant "TORONTO" (...)

-> using an Oracle function:              (...) name POSITION(6:15) "upper(:name)"  (...)

 

 

Load Fixed & Variable length data records

 

The example which use FIELDS TERMINATED BY "," allows variable length records, because the columns are delimited by "," (could be used any other sign). Sometimes we don't have delimiters and the columns are fixed length values. In this case the control file must be like:

 

load data
infile 'c:\DEPT.txt'
into table DEPT1
( DNAME POSITION(1:10), 
DEPTNO POSITION(12:13), 
LOC POSITION(15:23))

 

and the data file must have the content like:

 

ACOUNTING     10 OTTAWA
MANAGEMENT 20 MONTREAL
RESEARCH         30 HALIFAX
SALES                 40 QUEBEC

 

 

Loading in multiple table at once

 

Here is an example where the insertions are done on 2 tables (in my example DEPT1 and DEPT2 have identical structure):

 

load data
infile 'c:\DEPT.txt'
                                 into table DEPT1
( DNAME POSITION(1:10), 
DEPTNO POSITION(12:13), 
LOC POSITION(15:23))
                                  into table DEPT2
( DNAME POSITION(1:10), 
DEPTNO POSITION(12:13), 
LOC POSITION(15:23))

 

 

Improving the SQL*Loader performance

 

Here are some techniques to improve the load speed:

  • Using the direct load path (DIRECT=TRUE) 

  • Committing after a big  number of insertions (ROWS=<specify a number>)

 

 

In-Oracle.com  -> Oracle DBA -> Oracle Maintenance Tasks

-> SQL Loader in Oracle

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.