SQL Loader LOB Loading Tips

• For SQL*Loader conventional path loads, failure to load a particular LOB does not result in the rejection of the record containing that LOB; instead, the record ends up containing an empty LOB.

  For SQL*Loader direct-path loads, the LOB could be empty or truncated. LOBs are sent in pieces to the server for loading. If there is an error, the LOB piece with the error is discarded and the rest of that LOB is not loaded. In other words, if the entire LOB with the error is contained in the first piece, then that LOB column will either be empty or truncated.

• When loading from LOBFILEs specify the maximum length of the field corresponding to a LOB-type column. If the maximum length is specified, it is taken as a hint to help optimize memory usage. It is important that the maximum length specification does not underestimate the true maximum length.

• When using SQL*Loader direct-path load, loading LOBs can take up substantial memory. If the message "SQL*Loader 700 (out of memory)" appears when loading LOBs, internal code is probably batching up more rows per load call that which can be supported by your operating system and process memory. A workaround is to use the ROWS option to read a smaller number of rows per data save.

• Yo can also use the Direct Path API to load LOBs.

  See Also: Oracle9i Utilities Chapters 7 and 9. Chapter 9, "LOBS: Best Practices", Using SQL*Loader.