Considerations When Loading LOBs from LOBFILEs

Keep in mind the following when you load data using LOBFILEs:

  • Only LOBs and XML columns can be loaded from LOBFILEs.

  • The failure to load a particular LOB does not result in the rejection of the record containing that LOB. Instead, you will have a record that contains an empty LOB. In the case of an XML column, a null value will be inserted if there is a failure loading the LOB.

  • It is not necessary to specify the maximum length of a field corresponding to a LOB column. If a maximum length is specified, then SQL*Loader uses it as a hint to optimize memory usage. Therefore, it is important that the maximum length specification does not understate the true maximum length.

  • You cannot supply a position specification (pos_spec) when loading data from a LOBFILE.

  • NULLIF or DEFAULTIF field conditions cannot be based on fields read from LOBFILEs.

  • If a nonexistent LOBFILE is specified as a data source for a particular field, then that field is initialized to empty. If the concept of empty does not apply to the particular field type, then the field is initialized to null.

  • Table-level delimiters are not inherited by fields that are read from a LOBFILE.

  • When loading an XML column or referencing a LOB column in a SQL expression in conventional path mode, SQL*Loader must process the LOB data as a temporary LOB. To ensure the best load performance possible in these cases, refer to the guidelines concerning temporary LOB performance in Oracle Database SecureFiles and Large Objects Developer's Guide.