SQL*Loader allows you to load data from flat files into your database. It is a tool used in batch processing. This section will explain the 5 components of SQL*Loader and show you an example.

SQL*Loader is composed of 5 main files:

  1. Control file
  2. Data file
  3. Log file
  4. Discard file
  5. Bad file

The following diagram depicts the data flows of SQL*Loader. Click on each file to view an explanation of it.

The control file contains the specifications about the data being loaded, such as the location of the data file. The data file contains the data SQL*Loader loads into the database. SQL*Loader writes a bad file if data was rejected. If the control file specifies a discard file, then one will be written if data do not pass the criteria set forth in the control file. If specified at the command line prompt, SQL*Loader will write a log file, which is a record of the transactions made when SQL*Loader loaded the data into the database, such as the number of rows successfully loaded.

The control file specifies:

 Back to diagram

The data file contains the data to be loaded into the database. The data must either be text or binary format.
Back to diagram

"The log file records all of the transactions made by SQL*Loader for each load." [3] For example, it specifies the number of records successfully loaded, the number of records discarded, and the record specifications, such as column name and data type. A log file is written only if specified at the command line prompt, which I will show you how to do in the example at the end of this module.
Back to diagram

"The discard file holds records SQL*Loader finds that do not match the criteria for loading specified within the control file."[3] A discard file is written only if specified within the control file. Even if the control file specifies a discard file, and all records match the criteria, then SQL*Loader will not write a discard file.
Back to diagram

The bad file contains data that SQL*Loader rejected. The main reason that records get rejected are that they contain the wrong data type. Oracle automatically creates a bad file when records are rejected.
Back to diagram

This example will load 10 records from an external file into the Player table. You will create a control file and a data file.

  1. Start the database.
  2. Open Notepad.
  3. Type the following data into Notepad. The following data is the control file. The APPEND option will append the records to the PLAYER table. Save the file as example.ctl in the directory c:\orawin95\rdbms80\loader, file type "All Files."

    4. LOAD DATA

    INFILE ‘c:\orawin95\rdbms80\loader\example.dat’

    APPEND

    INTO TABLE PLAYER

    FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘ " ‘

    (PLAYER_NO,NAME,BEGIN_DATE,GOALS_HIT,COACH_ID)
  4. Open a new window in Notepad. Type the following data into Notepad. The following data is the data file. Save the file as example.dat in the directory c:\orawin95\rdbms80\loader, file type "All Files."

    5. 16,Bill,01-JAN-99,2,02

    17,Mo,01-FEB-99,7,02

    18,Bob,01-MAR-99,4,02

    19,Joe,10-APR-99,4,02

    20,Joy,01-JAN-99,1,01

    21,Pat,01-JAN-99,1,01

    22,Susan,01-FEB-99,8,01

    23,Rusty,01-JAN-99,7,01

    24,Chris,01-MAR-99,2,01

    25,Laurie,01-MAR-99,7,01

  5. Open a DOS window.
  6. At the command line prompt, type:

    Sqlldr80 userid=sam/sam control=c:\orawin95\rdbms80\loader\example.ctl log=c:\orawin95\rdbms80\loader\example.log

  7. Open Windows Explorer.
  8. Look for the log file (example.log):
    9.

    c:\orawin95\rdbms80\loader\example.log

  9. Once you have found example.log, open it in Notepad.

Return to Main