The following information applies to scalar fields. For nonscalar fields (column objects, LOBs, and collections), the
DEFAULTIF clauses are processed differently because nonscalar fields are more complex.
The results of a
DEFAULTIF clause can be different depending on whether the clause specifies a field name or a position.
DEFAULTIF clause specifies a field name, then SQL*Loader compares the clause to the evaluated value of the field. The evaluated value takes trimmed whitespace into consideration. See "Trimming Whitespace" for information about trimming blanks and tabs.
DEFAULTIF clause specifies a position, then SQL*Loader compares the clause to the original logical record in the data file. No whitespace trimming is done on the logical record in that case.
Different results are more likely if the field has whitespace that is trimmed, or if the
DEFAULTIF clause contains blanks or tabs or uses the
BLANKS parameter. If you require the same results for a field specified by name and for the same field specified by position, then use the
BLANKS option. The
BLANKS option instructs SQL*Loader not to trim whitespace when it evaluates the values of the fields.
The results of a
DEFAULTIF clause are also affected by the order in which SQL*Loader operates, as described in the following steps. SQL*Loader performs these steps in order, but it does not always perform all of them. Once a field is set, any remaining steps in the process are ignored. For example, if the field is set in Step 5, then SQL*Loader does not move on to Step 6.
SQL*Loader evaluates the value of each field for the input record and trims any whitespace that should be trimmed (according to existing guidelines for trimming blanks and tabs).
For each record, SQL*Loader evaluates any
WHEN clauses for the table.
If the record satisfies the
WHEN clauses for the table, or no
WHEN clauses are specified, then SQL*Loader checks each field for a
NULLIF clause exists, then SQL*Loader evaluates it.
NULLIF clause is satisfied, then SQL*Loader sets the field to
NULLIF clause is not satisfied, or if there is no
NULLIF clause, then SQL*Loader checks the length of the field from field evaluation. If the field has a length of 0 from field evaluation (for example, it was a null field, or whitespace trimming resulted in a null field), then SQL*Loader sets the field to
NULL. In this case, any
DEFAULTIF clause specified for the field is not evaluated.
If any specified
NULLIF clause is false or there is no
NULLIF clause, and if the field does not have a length of 0 from field evaluation, then SQL*Loader checks the field for a
DEFAULTIF clause exists, then SQL*Loader evaluates it.
DEFAULTIF clause is satisfied, then the field is set to 0 if the field in the data file is a numeric field. It is set to
NULL if the field is not a numeric field. The following fields are numeric fields and will be set to 0 if they satisfy the
DEFAULTIF clause is not satisfied, or if there is no
DEFAULTIF clause, then SQL*Loader sets the field with the evaluated value from Step 1.
The order in which SQL*Loader operates could cause results that you do not expect. For example, the
DEFAULTIF clause may look like it is setting a numeric field to
NULL rather than to 0.
As demonstrated in these steps, the presence of
DEFAULTIF clauses results in extra processing that SQL*Loader must perform. This can affect performance. Note that during Step 1, SQL*Loader will set a field to NULL if its evaluated length is zero. To improve performance, consider whether it might be possible for you to change your data to take advantage of this. The detection of NULLs as part of Step 1 occurs much more quickly than the processing of a
For example, a
CHAR(5) will have zero length if it falls off the end of the logical record or if it contains all blanks and blank trimming is in effect. A delimited field will have zero length if there are no characters between the start of the field and the terminator.
Also, for character fields,
NULLIF is usually faster to process than
DEFAULTIF (the default for character fields is NULL).