Oracle7 Server Utilities

Expanded Clauses and Their Functionality

Position Specification `pos_spec`

A position specification (pos_spec) gives the starting location for a field and, optionally, the ending location as well. A pos_spec is specified as follows:

The position must be surrounded by parentheses. The starting location may be specified as a column number, as * (next column), or *+n (next column plus an offset). The start and end locations may be separated with either a colon (:) or a dash (-).

Field Condition `field_condition`

A field condition compares a named field or an area of the record to some value. When the condition evaluates to true, the specified function is performed. For example, a true condition might cause the NULLIF function to insert a NULL data value, or cause DEFAULTIF to insert a default value. The field_condition is specified as follows:

The char_string and hex_string can be enclosed in either single quotation marks or double quotation marks. The hex_string is a string of hexadecimal digits, where each pair of digits corresponds to one byte in the field. The BLANKS keyword allows you to test a field to see if it consists entirely of blanks. It is necessary when you are loading delimited data and you cannot predict the length of the field, or when using a multi-byte character set that has multiple blanks.

There must not be any spaces between the operator and the operands on either side of it. Thus,

(1)='x'

is legal, while

(1) = 'x'

generates an error.

Column Name `column_name`

The column name you specify in a field condition must be one of the columns defined for the input record. It must be specified with double quotation marks if its name is a reserved word. See the section "Specifying Filenames and Database Objects"

for more details.

Datatype Specification `datatype_spec`

The datatype_spec tells SQL*Loader how to interpret the field in the input record. The syntax is as follows:

Precision vs. Length `precision length`

The precision of a numeric field is the number of digits it contains. The length of a numeric field is the number of byte positions on the record. The byte length of a ZONED decimal field is the same as its precision. However, the byte length of a (packed) DECIMAL field is (p+1)/2, rounded up, where p is the number's precision, because packed numbers contain two digits (or digit and sign) per byte.