Specifying the Position of a Data Field

To load data from the data file, SQL*Loader must know the length and location of the field. To specify the position of a field in the logical record, use the POSITION clause in the column specification. The position may either be stated explicitly or relative to the preceding field. Arguments to POSITION must be enclosed in parentheses. The start, end, and integer values are always in bytes, even if character-length semantics are used for a data file.

The syntax for the position specification (pos_spec) clause is as follows:

Specifying the Position of a Data Field describes the parameters for the position specification clause.

Table 10-1 Parameters for the Position Specification Clause

Parameter Description

start

The starting column of the data field in the logical record. The first byte position in a logical record is 1.

end

The ending position of the data field in the logical record. Either start-end or start:end is acceptable. If you omit end, then the length of the field is derived from the data type in the data file. Note that CHAR data specified without start or end, and without a length specification (CHAR(n)), is assumed to have a length of 1. If it is impossible to derive a length from the data type, then an error message is issued.

*

Specifies that the data field follows immediately after the previous field. If you use * for the first data field in the control file, then that field is assumed to be at the beginning of the logical record. When you use * to specify position, the length of the field is derived from the data type.

+integer

You can use an offset, specified as +integer, to offset the current field from the next position after the end of the previous field. A number of bytes, as specified by +integer, are skipped before reading the value for the current field.

You may omit POSITION entirely. If you do, then the position specification for the data field is the same as if POSITION(*) had been used.