Setting a Column to a Unique Sequence Number
The SEQUENCE parameter ensures a unique value for a particular column. SEQUENCE increments for each record that is loaded or rejected. It does not increment for records that are discarded or skipped.
SEQUENCE Parameter
The combination of column name and the SEQUENCE parameter is a complete column specification.
Table 10-6 describes the parameters used for column specification.
Table 10-6 Parameters Used for Column Specification
| Parameter | Description |
|---|---|
|
|
The name of the column in the database to which to assign the sequence. |
|
|
Use the |
|
|
The sequence starts with the number of records already in the table plus the increment. |
|
|
The sequence starts with the current maximum value for the column plus the increment. |
|
|
Specifies the specific sequence number to begin with. |
|
|
The value that the sequence number is to increment after a record is loaded or rejected. This is optional. The default is 1. |
If a record is rejected (that is, it has a format error or causes an Oracle error), then the generated sequence numbers are not reshuffled to mask this. If four rows are assigned sequence numbers 10, 12, 14, and 16 in a particular column, and the row with 12 is rejected, then the three rows inserted are numbered 10, 14, and 16, not 10, 12, and 14. This allows the sequence of inserts to be preserved despite data errors. When you correct the rejected data and reinsert it, you can manually set the columns to agree with the sequence.
Case study 3, Loading a Delimited Free-Format File, provides an example of using the SEQUENCE parameter. (See "SQL*Loader Case Studies" for information on how to access case studies.)