SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database.
Capabilities of SQL*Loader
- Can load data from multiple data files during the same load session
- Can load data into multiple tables during the same load session
- Can selectively load data (Records can be loaded based on the records’ values)
- Can deal with whitespace, delimiters, and null data
- Can manipulate the data before loading it, using SQL functions
- Can generate unique sequential key values in specified columns
- Can load data into large object (LOB) columns
- Can handle character set translation between the input data file and the database
- 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.