Data that is longer than SQL*Loader's maximum buffer size can be loaded on the direct path by using LOBs. You can improve performance when doing this by using a large
You could also load data that is longer than the maximum buffer size by using the
PIECED parameter, as described in the next section, but Oracle highly recommends that you use LOBs instead.
Declaring a column as
PIECED informs the direct path loader that a
LONG field might be split across multiple physical records (pieces). In such cases, SQL*Loader processes each piece of the
LONG field as it is found in the physical record. All the pieces are read before the record is processed. SQL*Loader makes no attempt to materialize the
LONG field before storing it; however, all the pieces are read before the record is processed.
The following restrictions apply when you declare a column as
This option is only valid on the direct path.
Only one field per table may be
PIECED field must be the last field in the logical record.
PIECED field may not be used in any
PIECED field's region in the logical record must not overlap with any other field's region.
PIECED corresponding database column may not be part of the index.
It may not be possible to load a rejected record from the bad file if it contains a
For example, a
PIECED field could span three records. SQL*Loader loads the piece from the first record and then reuses the buffer for the second buffer. After loading the second piece, the buffer is reused for the third record. If an error is discovered, then only the third record is placed in the bad file because the first two records no longer exist in the buffer. As a result, the record in the bad file would not be valid.