Oracle 10g Free Training - SQL*Loader Discarded and Rejected Records
In this tutorial you will learn about Discarded and Rejected Records - The Bad File, SQL*Loader Rejects, Oracle Database Rejects, The Discard File and Log File and Logging Information
Discarded and Rejected Records
Records read from the input file might not be inserted into the database. Such records are placed in either a bad file or a discard file.
The Bad File
The bad file contains records that were rejected, either by SQL*Loader or by the Oracle database. Some of the possible reasons for rejection are discussed in the next sections.
SQL*Loader Rejects
Datafile records are rejected by SQL*Loader when the input format is invalid. For example, if the second enclosure delimiter is missing, or if a delimited field exceeds its maximum length, SQL*Loader rejects the record. Rejected records are placed in the bad file.
Oracle Database Rejects
After a datafile record is accepted for processing by SQL*Loader, it is sent to the Oracle database for insertion into a table as a row. If the Oracle database determines that the row is valid, then the row is inserted into the table. If the row is determined to be invalid, then the record is rejected and SQL*Loader puts it in the bad file. The row may be invalid, for example, because a key is not unique, because a required field is null, or because the field contains invalid data for the Oracle datatype.
The Discard File
As SQL*Loader executes, it may create a file called the discard file. This file is created only when it is needed, and only if you have specified that a discard file should be enabled. The discard file contains records that were filtered out of the load because they did not match any record-selection criteria specified in the control file.
The discard file therefore contains records that were not inserted into any table in the database. You can specify the maximum number of such records that the discard file can accept. Data written to any database table is not written to the discard file.
Log File and Logging Information
When SQL*Loader begins execution, it creates a log file. If it cannot create a log file, execution terminates. The log file contains a detailed summary of the load, including a description of any errors that occurred during the load.
The other loading parameters such as the log file, the bad file, the discard file, and maximum number of errors are specified in the screen below.
The below screen is the final screen to initiate the data load process using the SQL*Loader. The data load can be scheduled to run immediately or at a specific time later
