When the control file definition specifies more fields for a record than are present in the record, SQL*Loader must determine whether the remaining (specified) columns should be considered null or whether an error should be generated.
If the control file definition explicitly states that a field's starting position is beyond the end of the logical record, then SQL*Loader always defines the field as null. If a field is defined with a relative position (such as
loc in the following example), and the record ends before the field is found, then SQL*Loader could either treat the field as null or generate an error. SQL*Loader uses the presence or absence of the
TRAILING NULLCOLS clause (shown in the following syntax diagram) to determine the course of action.
For example, consider the following data:
Assume that the preceding data is read with the following control file and the record ends after
INTO TABLE dept TRAILING NULLCOLS ( deptno CHAR TERMINATED BY " ", dname CHAR TERMINATED BY WHITESPACE, loc CHAR TERMINATED BY WHITESPACE )
In this case, the remaining
loc field is set to null. Without the
TRAILING NULLCOLS clause, an error would be generated due to missing data.
Case study 7, Extracting Data from a Formatted Report, for an example of using
TRAILING NULLCOLS (see "SQL*Loader Case Studies" for information on how to access case studies)