BADFILE clause names the file to which records are written when they cannot be loaded because of errors. For example, a record would be written to the bad file if a field in the data file could not be converted to the data type of a column in the external table. The purpose of the bad file is to have one file where all rejected data can be examined and fixed so that it can be loaded. If you do not intend to fix the data, then you can use the
NOBADFILE option to prevent creation of a bad file, even if there are bad records.
If you specify the
BADFILE clause, then you must supply either a directory object name or file name, or both. See "[directory object name:] [filename]".
NOBADFILE is specified, then the default is to create a bad file if at least one record is rejected. The name of the file will be the table name followed by
_%p, and it will have an extension of
Records that fail the
WHEN clause are not written to the bad file but are written to the discard file instead. Also, any errors in using a record from an external table (such as a constraint violation when using
INSERT INTO...AS SELECT... from an external table) will not cause the record to be written to the bad file.