Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Tuesday, 26 April 2016

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

Share this article :

0 comments:

Post a Comment