Oracle SQL*Loader 1 – Introduction

SQL*Loader is an Oracle-supplied utility that allows you to load data from a flat file into one or more database tables.

The basis for almost everything you do with SQL*Loader is a file known as the control file. The SQL*Loader control file is a text file into which you place a description of the data to be loaded. You also use the control file to tell SQL*Loader which database tables and columns should receive the data that you are loading.

Once you have a data file to load and a control file describing the data contained in that data file, you are ready to begin the load process. You do this by invoking the SQL*Loader executable and pointing it to the control file that you have written. SQL*Loader reads the control file to get a description of the data to be loaded. Then it reads the input file and loads the input data into the database.

The SQL*Loader 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 data file
  •     The format of the records in the input data file
  •     The name of the 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 locations of the bad file and the discard file

The Log File

The log file is a record of SQL*Loader's activities during a load session. It contains information such as the following:

  •     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 datatypes 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

Bad File

Whenever you insert data into a database, you run the risk of that insert failing because of some type of error. Integrity constraint violations undoubtedly represent the most common type of error. However, other problems, such as the lack of free space in a tablespace, can also cause insert operations to fail. Whenever SQL*Loader encounters a database error while trying to load a record, it writes that record to a file known as the bad file.

Discard File

Discard files, on the other hand, are used to hold records that do not meet selection criteria specified in the SQL*Loader control file. By default, SQL*Loader will attempt to load all the records contained in the input file. You have the option, though, in your control file, of specifying selection criteria that a record must meet before it is loaded. Records that do not meet the specified criteria are not loaded, and are instead written to a file known as the discard file.


You can leave a response, or trackback from your own site.

Leave a Reply