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 dname
and 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.
The TRAILING
NULLCOLS
clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.
For example, consider the following data:
10 Accounting
Assume that the preceding data is read with the following control file and the record ends after dname:
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)