SQL *Loader

SQL *Loader utility is used to load data from external files into oracle tables.  These files can be local files, network files, tape or names pipe. You can SQL *Loader to do the following:

• Load multiple data files in single load session
• Load multiple tables in a single load session
• Selective data load
• Manupulate data before loading using SQL function
• Can generate sequential key value
• Can load LOB and collection using secondary data files

SQL loader supports direct path loading that improves loading performance over conventional path loading.   

SQL *Loader supports:

• Nested Table ( Collection )
• VARRAY ( Collection )
• BLOB, CLOB, NBLOB, NCLOB ( LOB type )
• Partitioned Objects ( partitioned and none partitioned tables)

SQL *Loader uses control file which control SQL loader behavior and one or more data file. The output of SQL *Loader is log file, bad file and a possibly a discard file. SQL *Loader uses command line parameters, if these parameter are not frequently change then these parameter can be grouped together in a parameter file. Certain parameters can also be placed in control file using the OPTION clause.

SQL *Loader Control File

It is simple text file uses SQL *Loader understandable format. This file contains three section, first section more on session wide information

• Where to find data ( INFILE clause specify the location of input data )
• Global Option such as skip records, rows and bind size etc)

Second section consist of one or more INTO TABLE. Each INTO TABLE tells about columns and table where to be data loaded.

Third section is optional and normally this section contains the input data.

Input Data or Data file

Data file can be fixed record format, variable record format, or stream record format. You can specify record format using INFILE parameter. if no record format is specified then by default stream record format used. During loading process data fields in data files are converted into columns in the database. This is conceptually similar for conventional and direct path load but implementation are different.

Bad File

These files contains records that are not inserted into database. Bad file contains the records rejected either by SQL *Loader or Database. SQL loader rejects the records for invalid input format such as delimiter missing or field exceeds its maximum length.  Database rejects records if it violating constraint such as NOT NULL column is trying to populate with NULL. In both the scenario SQL *Loader or Database rejection, SQL *Loader put this record in bad file.

Discard File

SQL *Loader execution may create Discard file, this file is created only when it requires. SQL *Loader put filtered records (if filter condition specified in control file) in discard file. In simple words, Discard file contains record that are neither inserted into a table nor rejected.You can specify maximum discarded record limit.

Log File

SQL * Loader execution create the log file, if it can not create log file then SQL Loader process gets terminated. Log file contains detail summary of load and error, if any.

External table Vs SQL *Loader

SQL Loader and External Loader using the very similar in record parsing, so there is not major difference in term of performance. Due to architectural difference one is better than other are as follows:

• External table works better if you want to transform the data or you want get the benefit of parallel processing without splitting the data file physically.
• SQL *Loader works better when you don’t want to transform the data or want to load data remotely.