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