Viewing / Altering The INF Tables After SQL Loader Has Been Run#

Once the data has been loaded into the interface tables we can use SQL Plus to view and/or alter the individual rows, or a group of rows. The following script is an example of how to count the number of rows that exist in the PERSONALS interface table:

SELECT COUNT (*) 
FROM INF_CV_PERSONALS;
COUNT (*)
---------
        0

This information can be used to verify that all the records in the comma-delimited file were loaded successfully into the interface table. The total number of records within the interface table should be the same number of records in the flat file. If the numbers do not match, the SQL Loader program would have rejected some of the records.

SQL Loader Rejects#

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, if one has been specified within the control file. If you chose to capture rejected records is a “Bad file” you must specify the name of the file within the SQL Loader control file. The following line will capture the records into a file called BADFILE.RJT.

BADFILE BADFILE.RJT

You may have to qualify the location of the bad file by using a line similar to the following:

BADFILE C:\HIGHLINE\P2K\CONVERSION\REJECT\BADFILE.RJT

Oracle Rejects#

After a record is accepted for processing by SQL Loader, a row is sent to Oracle for insertion. If Oracle determines that the row is valid, then the row is inserted into the database. If not, the record is rejected, and SQL Loader puts it in the bad file. The row may be rejected, for example, because a key is not unique, because a required field is NULL, or because the field contains invalid data for the Oracle data type.

If the data can be evaluated according to the WHEN-clause criteria (even with unbalanced delimiters) then it is either inserted or rejected.

If a record is rejected on insert, then no part of that record is inserted into any table. For example, if data in a record is to be inserted into multiple tables, and most of the inserts succeed, but one insert fails; then all the inserts from that record are rolled back. The record is then written to the bad file, where it can be corrected and reloaded. Previous inserts from records without errors are not affected.

SQL Loader Discards#

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 of the record-selection criteria specified in the control file.

The discard file therefore contains records that were not inserted into any table, up to a specifiable maximum. If a record's data is written to any table, it is not written to the discard file.

The discard file is written in the same format as the data file. The discard data can be loaded with the existing control file, after any necessary editing or correcting. These discarded records can, optionally, be placed in a reject file. However, the name and location of the reject file must be added to the control file prior to running SQL Loader. The following line would need to exist after the INFILE setting in the control file:

DISCARDFILE 'BADPERS.RJT'
DISCARDMAX 999

This identifies the reject file as being located in the current work directory and named BADPERS.RJT. If the number of rejected records exceeds 999, the SQL Loader will abort the load process for the remaining records in the data file.

---

Notes #

Click to create a new notes page