Specifying the Bad File

When SQL*Loader executes, it can create a file called a bad file or reject file in which it places records that were rejected because of formatting errors or because they caused Oracle errors. If you have specified that a bad file is to be created, then the following applies:

  • If one or more records are rejected, then the bad file is created and the rejected records are logged.

  • If no records are rejected, then the bad file is not created.

  • If the bad file is created, then it overwrites any existing file with the same name; ensure that you do not overwrite a file you want to retain.


    On some systems, a new version of the file may be created if a file with the same name already exists.

To specify the name of the bad file, use the BADFILE clause. You can also specify the bad file from the command line with the BAD parameter described in "Command-Line Parameters for SQL*Loader".

A file name specified on the command line is associated with the first INFILE clause in the control file, overriding any bad file that may have been specified as part of that clause.

The bad file is created in the same record and file format as the data file so that you can reload the data after you correct it. For data files in stream record format, the record terminator that is found in the data file is also used in the bad file.

The syntax for the BADFILE clause is as follows:

The BADFILE clause specifies that a directory path or file name, or both, for the bad file follows. If you specify BADFILE, then you must supply either a directory path or a file name, or both.

The directory parameter specifies a directory path to which the bad file will be written.

The filename parameter specifies a valid file name specification for your platform. Any spaces or punctuation marks in the file name must be enclosed in single quotation marks. If you do not specify a name for the bad file, then the name defaults to the name of the data file with an extension or file type of .bad.