Oracle7 Server Utilities
Specifying the Discard File
As SQL*Loader executes, it may create a discard file for records that do not meet any of the loading criteria. The records contained in this file are called discarded records. Discarded records do not satisfy any of the WHEN clauses specified in the control file. These records are different from rejected records. Discarded records do not necessarily have any bad data. No insert is attempted on a discarded record.
The discard file is created according to the following rules:
- A discard file is only created if a discard filename is specified.
- A discard file is only created if one or more records fail to satisfy any of the WHEN clauses specified in the control file, even if it has been specified.
- If the discard file is created, it overwrites an existing file with the same name.
- If no records are discarded, then a discard file is not created.
- If a discard file is not created, then an existing file with the same name remains intact.
Suggestion: If a file exists with the same name as the discard file that SQL*Loader may create, delete or rename it before running SQL*Loader.
To create a discard file, use any of the following options:
| In a Control File
|
| On the Command Line
|
| DISCARDFILE filename
|
| DISCARD
|
| DISCARDDN filename
|
| DISCARDMAX
|
| DISCARDS
|
|
|
| DISCARDMAX
|
|
|
Note that you can request the discard file directly with a parameter specifying its name, or indirectly by specifying the maximum number of discards.
Using a Control-File Definition
To specify the name of the file, use the DISCARDFILE or DISCARDDN keyword, followed by the filename.
where:
| DISCARDFILE or DISCARDDN | Either keyword may be used.
|
| discard_file_name | Any valid filename, specifying a file to receive discard records. |
Note: Filenames that include spaces or punctuation marks should be enclosed in single quotation marks. For more details on filename specification, see "Specifying Filenames and Database Objects" ![[*]](jump.gif)
.
The default filename is the name of the datafile, and the default file extension or file type is DSC. A discard filename specified on the command line overrides one specified in the control file. If a discard file with that name already exists, it is either overwritten or a new version is created, depending on your operating system.
The discard file is created with the same record and file format as the datafile. So it can easily be used for subsequent loads with the existing control file, after changing the WHEN clauses or editing the data.
Examples of How to Specify a Discard File
In the first example, you specify a discard file with filename CIRCULAR and default file extension or file type of DSC:
DISCARDFILE CIRCULAR
In this example, you specify a file extension or file type of MAY:
DISCARDDN NOTAPPL.MAY
In the next example, you specify a full path to filename FORGET.ME:
DISCARDFILE '/DISCARD_DIR/FORGET.ME'