Format checks on files being loaded
In any data load processing mode, the system checks incoming data against the format requirements of target columns, including data type, length, codelist values (if the column is associated with a codelist), and the nullable and check constraints.
When you configure File Watcher for a table, you define the Reported Errors parameter. During a data load, the system counts errors in the file until it reaches the number set in this parameter plus one. It then stops processing the file, reports the errors in the log file, and the data load fails. If the number of errors detected for each file in the text data load is not more than Reported Errors, then the data load completes with a Warning status.
Records that are rejected are captured and validated for other errors. All the errors found in each record are reported in a file called ComprehensiveErrRpt.csv.
The error file contains one row for each record with an error that says "ORIGINAL ERROR" in the Column Name column, another row for the original error, and additional rows for any other errors it finds on the same record. For the purposes of calculating the number of reported errors, the logic treats each record that can't be loaded as one error, even if it contains multiple errors.
Table 8-9 shows the error file entries for two records. Record 1 has two errors and Record 32 has one error. The string "CDR_W37_1D0156B9.TXT_TV486627301.Name" represents the full path of the erroring field, Name, for Record 1. The first part, "CDR_W37_1D0156B9," is the schema name and the second, "TXT_TV486627301," is the view based on the target table.
Table 8-9 Error file example
TABLE_NAME | FILE_NAME | REC_NUM | COLUMN_NAME | VALUE | ERROR_MESSAGE |
---|---|---|---|---|---|
DEMOG |
DEMOG.txt |
1 |
ORIGINAL_ERROR |
Captured in the TextLoad.log |
ORA-20100: ORA-01400: cannot insert NULL into ("CDR_W37_1D0156B9.TXT_TV486627301.NAME") |
DEMOG |
DEMOG.txt |
1 |
NAME |
ORA-01400: cannot insert NULL into ("CDR_W37_1D0156B9.TXT_TV486627301.NAME") |
|
DEMOG |
DEMOG.txt |
1 |
HT |
ORA-01400: cannot insert NULL into ("CDR_W37_1D0156B9.TXT_TV486627301.HT") |
|
DEMOG |
DEMOG.txt |
32 |
ORIGINAL_ERROR |
Captured in the TextLoad.log |
ORA-20100: ORA-02290: check constraint (("CDR_W37_1D0156B9.TXT_TV486627301 .AGE_CK) violated |
DEMOG |
DEMOG.txt |
32 |
AGE |
05 |
ORA-02290: check constraint (("CDR_W37_1D0156B9.TXT_TV486627301 .AGE_CK) violated |
Parent topic: Data processing during data loading