SQL Loader

SQL*Loader is an oracle utility to load large amount of data in to oracle database. It reads the data from flat file and insert into table.

SQL*Loader takes as input a control file, which controls the behavior of SQL*Loader, and one or more data files.

Output of the SQL*Loader is an Oracle database (where the data is loaded), a log file, a bad file, and potentially a discard file.

Data load methods

SQL*Loader provides two methods for loading data

Conventional Load: Data reads from the data file and inserted into database using standard INSERT statements.

Direct Load: Rather than load data using a series of INSERT statements, data being written directly into the database data files.

Direct Load

All table level triggers are disabled during direct path load

During Direct path load some integrity constraints are automatically disabled, others are not.

The following constraints remain in force

NOT NULL

Unique

Primary Keys

The following constraints disabled

Check constraints

Foreign keys

Architecture

Log file

The log file is a record of SQL*Loader’s activities during a load session. It contains the following information

The names of the control file, log file, bad file, discard file, and data file

The values of several command-line parameters

A detailed breakdown of the fields and data types in the data file that was loaded

Error messages for records that cause errors

Messages indicating when records have been discarded

A summary of the load that includes the number of logical records read from the data file, the number of rows rejected because of errors, the number of rows discarded because of selection criteria, and the elapsed time of the load

Messages indicating when records have been discarded

A summary of the load that includes the number of logical records read from the data file, the number of rows rejected because of errors, the number of rows discarded because of selection criteria, and the elapsed time of the load

File extension is “.log”

Bad file Vs Discard file

Bad file

Bad file contains the records rejected either by the sql*loader or oracle.

Ex:

If a delimited field exceeds the Specified length

Unique constraint violation

Data type mismatch

File extension is “.bad”

Discard File

The discard file contains records that were filtered out of the load because they did not match any record-selection criteria specified in the control file.

Ex:

WHEN condition fails

File extension is “.disc”

Invoking SQL*Loader

Sqlldr is a command used to invoke the sqlloader

SQL*Loader is not case sensitive

Parameters on the command line may be separated by spaces or commas

Ex:

sqlldr system/manager,control=product.ctl,log=product.log

sqlldr system/manager, control=product.ctl, log=product.log

sqlldr system/manager control=product.ctl log=product.log

USERID={username[/password][@net_service_name]|/}

CONTROL=control_file_name

LOG=path_file_name

BAD=path_file_name

DATA=path_file_name

DISCARD=path_file_name

DISCARDMAX=logical_record_count

SKIP=logical_record_count

LOAD=logical_record_count

ERRORS=insert_error_count

SILENT=[(]keyword[,keyword...][)]

DIRECT={TRUE | FALSE}

PARFILE=path_file_name

Control file

Control file is the key to load the data. It provides the following information to SQL*Loader

Name and Location of input data file

Format of the records in the input data file

Name of the table(s) to be loaded

Correspondence between fields in file and columns in table

Selection criteria defining which records from the input file contain data to be inserted into the destination database tables

Names and location of bad and discard files

Control file has 3 main sections with 2 mandatory and 1 optional.

Session wide information (Mandatory)

Table and field list information (Mandatory)

Input data (Optional)

File extension is “.ctl”

The LOAD Statement tells the loader what data to load, where to get it from, where to put it, and how to interpret it

LOAD DATA syntax

{LOAD} [DATA]

[INFILE clause [INFILE clause...]]

[INSERT | APPEND | REPLACE | TRUNCATE]

INTO TABLE clause [INTO TABLE clause...]

[BEGINDATA]

Specifying the Target Tables

                     INTO TABLE table_name

{INSERT | REPLACE | TRUNCATE | APPEND}

[OPTIONS (FILE=database_filename)]

[WHEN field_conditions]

[FIELDS [delimiter_description]]

[TRAILING [NULLCOLS]

[SKIP skip_count]

(field_list)

Advantages

Can read multiple files in a single load session

It supports a number of data types

Can load into multiple tables

Allows to use oracle functions to manipulate data

Can load data into object tables, varrays and nested tables

Can load into large object (LOB) columns

Disadvantages

Low integration with pl/sql

Can’t perform the updates while loading

Hard to do conditional Translation

Does not have any direct file operations but can be done with Unix Schell script

Loading Variable length data 

LOAD DATA

INFILE *

INTO XX_SUP_STG

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

(SUP_NUM, SUP_NAME, LOACTION)

BEGINDATA

1021,”US acct LTD”,USA

1202,Voda,”IND”

LOAD DATA statement specifies beginning of the control file

INFILE * specifies that the data is found in the control file and not in an external file.

INTO TABLE specifies to load data into table

FIELDS TERMINATED BY specifies that the data is terminated by commas, but may also be enclosed by quotation marks.

BEGINDATA specifies the beginning of the data.

Loading Fixed length data

LOAD DATA

INFILE “XX_DEPT_DATA.txt”

INTO XX_SUP_STG

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

(SUP_NUM, SUP_NAME, LOACTION)

LOAD DATA statement specifies beginning of the control file

Name of the file containing data .

INTO TABLE specifies to load data into table

Lines 4 and 5 identify a column name and the location of the data in the data file to be loaded into that column. SUP_NUM, SUP_NAME, LOACTION are names of columns in table XX_DEPT_STG. The datatypes (INTEGER EXTERNAL, CHAR) identify the datatype of data fields in the file, not of corresponding columns in the EMP table.

Loading Combined physical records

LOAD DATA

INFILE ‘XX_PO_DATA.dat'

DISCARDFILE ‘XX_PO_DATA.dsc'

DISCARDMAX 999

REPLACE

CONTINUEIF THIS (1) = '*'

INTO TABLE XX_PO_STG

(SUP_NUM      POSITION(01:04) CHAR

, PO_NUM        POSITION(06:15)  CHAR

, PO_TYPE         POSITION(16:21)   CHAR

,QUANTITY     POSITION(22:30) DECIMAL  EXTERNAL

,PRICE               POSITION(30:38) DECIMAL  EXTERNAL

)

DISCARDFILE specifies the name of the discard file as XX_PO_DATA.dat

DISCARDMAX specifies that a maximum of 999 discards are allowed before terminating the run.

REPLACE specifies that if there is data in the table being loaded, SQL*Loader should delete that data before loading new data.

CONTINUEIF specifies that if an asterisk is found in column 1 of the current record, then the next physical record after that record should be appended to it to form the logical record. Note that column 1 in each physical record should then contain either an asterisk or a non-data value

Load data into multiple tables

LOAD DATA

INFILE ‘XX_PO_DATA.dat'

DISCARDFILE ‘XX_PO_DATA.dsc'

APPEND

INTO TABLE XX_PO_STG

WHEN PO_NUM != ‘’

(SUP_NUM      POSITION(01:04) CHAR

, PO_NUM        POSITION(06:15)  CHAR

,PO_TYPE         POSITION(16:21)  CHAR

,QUANTITY     POSITION(22:30)  DECIMAL  EXTERNAL

,PRICE               POSITION(30:38) DECIMAL  EXTERNAL

)

INTO TABLE XX_SUP_STG

WHEN SUP_NUM != ‘’

(SUP_NUM      POSITION(01:04) CHAR

,SUP_NAME    POSITION(39:45) CHAR

,LOACTION      POSITION(45:50) CHAR

)

APPEND specifies loader to add the data for any data exists in table

multiple INTO loads data into 2 tables

WHEN loads into XX_PO_STG table only with non blank po numbers and loads into XX_SUP_STG table only with non blank supplier numbers

Load multiple files into table

LOAD DATA

INFILE ‘XX_PO_DATA.dat‘

INFILE ‘XX_PO_DATA1.dat‘

APPEND

INTO TABLE XX_PO_STG

(SUP_NUM      POSITION(01:04) CHAR

, PO_NUM        POSITION(06:15)  CHAR

,PO_TYPE         POSITION(16:21)  CHAR

,QUANTITY     POSITION(22:30)  DECIMAL  EXTERNAL

,PRICE               POSITION(30:38) DECIMAL  EXTERNAL

)

Two INFILE statements load data into single table