Oracle7 Server Utilities

Specifying the Position of a Data Field

To load data from the datafile SQL*Loader must know a field's location and its length. To specify a field's position in the logical record, use the POSITION keyword 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.

where:

start The starting column of the data field in the logical record. The first character position in a logical record is 1.
end Indicates the ending position of the data field in the logical record. Either start:end or start-end is acceptable. If end is omitted, the length of the field is derived from the datatype in the datafile. (See the sections on each datatype. Note that CHAR data specified without a start and end is assumed to be length 1.) If it is impossible to derive a length from the datatype, an error message results.
* Indicates that the data field follows immediately after the previous field. If * is used for the first data field in the control file, the field is assumed to be at the beginning of the logical record. When * is used for position, the length of the field is derived from the datatype.
+n An offset, specified as +n, may be used with * to offset this field from the previous one. n characters are skipped before reading the value for this field.

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

For example

ENAME  POSITION (1:20)  CHAR

EMPNO  POSITION (22-26) INTEGER EXTERNAL

ALLOW  POSITION (*+2)   INTEGER EXTERNAL TERMINATED BY "/"

Column ENAME is character data in positions 1 through 20, followed by column EMPNO, which is presumably numeric data in columns 22 through 26. Column ALLOW is offset from the end of EMPNO by +2. So it starts in column 28 and continues until a slash is encountered.

Using POSITION with Data that Contains TABs

When you are determining field positions, be alert for TABs in the datafile. The following situation is highly likely when using SQL*Loader's advanced SQL string capabilities to load data from a formatted report:

You look at a printed copy of the report, carefully measuring all of the character positions, and create your control file.

The load then fails with multiple "invalid number" and "missing field" errors.

These kinds of errors occur when the data contains TABs. When printed, each TAB expands to consume several columns on the paper. In the datafile, however, each TAB is still only one character. As a result, when SQL*Loader reads the datafile, the POSITION specifications are wrong.

To fix the problem, inspect the datafile for tabs and adjust the POSITION specifications, or else use delimited fields.

The use of delimiters to specify relative positioning of fields is discussed in detail beginning . Especially note how the delimiter WHITESPACE can be used.

Using POSITION with Multiple Table Loads

In a multiple table load, you specify multiple INTO TABLE clauses. When you specify POSITION(*) for the first column of the first table, the position is calculated relative to the beginning of the logical record. When you specify POSITION(*) for the first column of subsequent tables, the position is calculated relative to the last column of the last table loaded.

Thus, when a subsequent INTO TABLE clause begins, the position is not set to the beginning of the logical record automatically. This allows multiple INTO TABLE clauses to process different parts of the same physical record. For an example, see the second example in the section "Extracting Multiple Logical Records" beginning .

A logical record may contain data for one of two tables, but not both. In this case, you would reset POSITION. Instead of omitting the position specification or using POSITION(*+n) for the first field in the INTO TABLE clause, use POSITION(1) or POSITION(n).

Some examples follow:

SITEID  POSITION (*) SMALLINT

SITELOC POSITION (*) INTEGER

If these were the first two column specifications, SITEID would begin in column1, and SITELOC would begin in the column immediately following.

ENAME  POSITION (1:20)  CHAR

EMPNO  POSITION (22-26) INTEGER EXTERNAL

ALLOW  POSITION (*+2)   INTEGER EXTERNAL TERMINATED BY "/"

Column ENAME is character data in positions 1 through 20, followed by column EMPNO which is presumably numeric data in columns 22 through 26. Column ALLOW is offset from the end of EMPNO by +2, so it starts in column 28 and continues until a slash is encountered.

start	The starting column of the data field in the logical record. The first character position in a logical record is 1.
end	Indicates the ending position of the data field in the logical record. Either start:end or start-end is acceptable. If end is omitted, the length of the field is derived from the datatype in the datafile. (See the sections on each datatype. Note that CHAR data specified without a start and end is assumed to be length 1.) If it is impossible to derive a length from the datatype, an error message results.
*	Indicates that the data field follows immediately after the previous field. If * is used for the first data field in the control file, the field is assumed to be at the beginning of the logical record. When * is used for position, the length of the field is derived from the datatype.
+n	An offset, specified as +n, may be used with * to offset this field from the previous one. n characters are skipped before reading the value for this field.