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 STREAMSIZE value.
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.
The PIECED parameter can be used to load data in sections, if the data is in the last column of the logical record.
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 PIECED:
This option is only valid on the direct path.
Only one field per table may be PIECED.
The PIECED field must be the last field in the logical record.
The PIECED field may not be used in any WHEN, NULLIF, or DEFAULTIF clauses.
The PIECED field's region in the logical record must not overlap with any other field's region.
The 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 PIECED field.
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.