Oracle7 Server Utilities
Setting a Column to Null or Zero
If you want all inserted values for a given column to be null, omit the column's specifications entirely. To set a column's values conditionally to null based on a test of some condition in the logical record, use the NULLIF clause, described in this section. To set a numeric column to zero instead of NULL, use the DEFAULTIF clause, described next.
DEFAULTIF Clause
Using DEFAULTIF on numeric data sets the column to zero when the specified field condition is true. Using DEFAULTIF on character data (CHAR, DATE, or numeric EXTERNAL) data sets the column to null. See "Specifying Field Conditions" ![[*]](jump.gif)
for details on the conditional tests.
DEFAULTIF field_condition
A column may have both a NULLIF clause and a DEFAULTIF clause, although this often would be redundant.
Note: The same effects can be achieved with the SQL string and the DECODE function. See "Applying SQL Operators to Fields" .
NULLIF Keyword
Use the NULLIF keyword after the datatype and optional delimiter specification, followed by a condition. The condition has the same format as that specified for a WHEN clause. The column's value is set to null if the condition is true. Otherwise, the value remains unchanged.
NULLIF field_condition
The NULLIF clause may refer to the column that contains it, as in the following example:
COLUMN1 POSITION(11:17) CHAR NULLIF (COLUMN1 = "unknown")
This specification may be useful if you want certain data values to be replaced by nulls. The value for a column is first determined from the datafile. It is then set to null just before the insert takes place. Case 6 includes more examples of the NULLIF clause.
Note: The same effect can be achieved with the SQL string and the NVL function. See "Applying SQL Operators to Fields" .
Null Columns at the End of a Record
When the control file 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. The TRAILING NULLCOLS clause, described , tells SQL*Loader how to proceed in this case.