SQL*Loader Concepts

By | February 9, 2017 | 1,602 views | Category: SQL*Loader Tags:

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database.

Capabilities of SQL*Loader

  1. Can load data from multiple data files during the same load session
  2. Can load data into multiple tables during the same load session
  3. Can selectively load data (Records can be loaded based on the records’ values)
  4. Can deal with whitespace, delimiters, and null data
  5. Can manipulate the data before loading it, using SQL functions
  6. Can generate unique sequential key values in specified columns
  7. Can load data into large object (LOB) columns
  8. Can handle character set translation between the input data file and the database
  9. Does sophisticated error reporting which greatly aids troubleshooting

SQL* Loader Architecture

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

Control File: The SQL*Loader control File is the key to any load process.

  • The control file provides the following information to SQL*Loader
  • The name and location of the input file
  • The format of the records in the input data file
  • The name of table or tables to be loaded
  • The correspondence between the fields in the input record and the columns in the database tables being loaded
  • Selection criteria defining which records from the input file contain data to be inserted into the destination database tables
  • The names and location of the bad file and the discard file

Data File: The data file contains the data to be loaded in a specific format.
Bad File: The bad file contains records rejected, either by SQL*Loader or by Oracle.
Log File: The log file contains a detailed summary of the load, including a description of any errors that occurred during the load.
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.

WOW! Did you like this post? We'll send more interesting posts like SQL*Loader Concepts to you!
Enter your Email Address: