Oracle SQL*Loader: The Definitive Guide

Chapter 7. Validating and Selectively Loading Data

SQL*Loader can create two types of output data files during a load: the bad file and the discard file. The bad file is where SQL*Loader writes input records that cause errors. The discard file is where SQL*Loader writes records that do not match conditions that you specify in your LOAD statement. We first mentioned these files in Chapter 1. In this chapter, we’ll delve more deeply into the use of the bad and discard files.

Handling Rejected Records

While loading data from an input data file into an Oracle database, SQL*Loader may encounter records that cause errors, and that consequently cannot be loaded. There are many reasons why a given record might cause an error. Some of the more common reasons include the following:

• The fields in the record may not match the field descriptions in the LOAD statement.

• The record may cause a database integrity constraint to be violated.

• An Oracle server error might occur due to lack of free space or some other problem.

• An insert trigger may fire on the table being loaded, and that trigger may raise an application error, causing the record to be rejected.

Regardless of the underlying cause, SQL*Loader cannot load a record that causes an error. When a record causes an error, SQL*Loader does two things. It records the error in the log file, and it writes a copy of the record causing the error to the bad file. It will then continue with the load process.

Naming the Bad File

By default, SQL*Loader ...