Sql *Loader

Sql *Loader is a high speed data loading utility supplied by Oracle that loads data from external files into table in an Oracle Database.
It can accepts data in variety of formats, can perform transformation, filtering and can load into multiple tables from multiple files in same loading session.

Requirements- For Sql *Loader

• Tables to be loaded must already exists in the Database. SQL *Loader never creates tables, it loads existing tables.
• Table may be empty or may already contain data.
• Privileges:
  INSERT privilege
  DELETE privilege, when using REPLACE or TRUNCATE insert option.

Files used in Sql *Loader

• Control File – .ctl file
• Data File – .dat file
• Log File – .log file
• Bad File – .bad file
• Discard File – .dis file

Control File

It controls the Behavior of Sql *Loader

• Source of Data to be loaded
• Destination of Data to be loaded
• Filtering of Data before Loading
• Transformation of Data before Loading
• Relating the Data file fields to table columns

Log File

It is a record of SQL *Loader’s activities during a load session

• Control, Data, Bad, Discard file Name.
• Values of several command line parameters.
• Detailed breakdown of the fields & datatypes in data file that was loaded.
• Error Message for records that cause error.
• Message indicating when records have been discarded.
• Summary of the Load (no: of records read from file, no: of rows rejected because of errors, no: of rows discarded & elapsed time of load)
• Data File – Contains the data to be loaded, it is optional
• Discard File – Contains the data that are discarded by ‘when’ clause in control file, it is optional.
• Bad File – Contains the data which are not loaded due to some errors, it is not optional, if any one error occurred, SQL *Loader will create the bad file and write the offending input records into it.

SQL *Loader Overview

 

 

Loading Method

Basically there are 3 Loading Methods

• Conventional Path Load
• Direct Path Load
• External Table Load( from Oracle 9i).

Conventional Path Load

• Bind Array (Row Insert) is created by Sql *Loader based on field specification in control file
• Data from the Bind Array are then insert into the table by the DB server, if data satisfies with the corresponding column datatype

Direct Path Load

• Parses the data according to the field specification in Control File
• Converts the data to Column datatype (not to field datatype in .ctl file) and builds Column array.
• Column array is passed to block formatter
• Newly formatted blocks are directly written directly to the database file bypassing most SQL Processing
  PARALLEL loading is possible.

PARALLEL Loading

• Setup has to done, as the text file must be broken into several smaller files
• We can run several SQLLDR session in parallel for each broken data files
• It will increase the performance, reduce the time for loading

Advantage & Disadvantage of Direct Path Load

Advantage :

• Very High Speed

Disadvantage :

• Cannot Load Object Types, Collection Types (Nested Table, VARRAY),LOB(CLOB,NCLOB,BLOB, BFILE)

Field Conversion

 

Record Filtering

 

Command Line Parameters

• BAD – Specifies the file in which all bad data is kept. The default filename is the control filename with a .bad extension.
• BINDSIZE – Size of the bind array in bytes. System dependent.
• CONTROL – Specifies the name of control file.
• DATA – Specifies the file that contains all data to be loaded.
• DIRECT – If set to TRUE Direct Path Load is used. Otherwise Conventional Path will be used. Default value is FALSE.
• DISCARD – Specifies the file where all discarded data is kept

• DISCARDMAX – The maximum no: of invalid records, which is not satisfying the ‘when’ clause in control file, that may be encountered before SQL *Loader session is stop. Default value is ALL.
• ERRORS – Specifies how many total errors can be encountered before SQL *Loader session is to stop. Default value is 50.
• LOAD – Specifies the maximum no: of records to load before stopping. Default Value is ALL.
• LOG – Specifies the log file’s name, where information on success or failure of the Loading session is reported. The Default filename is control_filename.log.

• PARALLEL – If set to TRUE, loads are performed in parallel where possible. Default value is FALSE
• PARFILE – An additional file that contains more parameter specification.
• ROWS – No: of rows to put in the path bind array, for Conventional path load. For Direct path load, ROWS specifies the no: of rows to read before a data save is performed. Default value is 64 in Conventional Path.
• SKIP – No: of record to skip before starting the load. This parameter is important for restarting a load process after stopping an earlier session. Useful in Recovery from failure.
• USERID – Specifies the username and password for the user conducting the SQL *Loader session
• SILENT – Allows to suppress various header and feedback messages that SQL *Loader normally displays during a Load session.

Keyword for use with SILENT parameter

• DISCARDS – Suppresses the discarded messages
• ERRORS – Suppresses the error message
• FEEDBACK – Suppresses the “commit point reached” messages.
• HEADER – Suppresses the messages that SQL *Loader displays on the screen when we first launch the executable.
• PARTITION – Suppresses the per-partition statistics, when loading a direct path load of a partitioned table.