Loading LOB Data from LOBFILEs

LOB data can be lengthy enough so that it makes sense to load it from a LOBFILE instead of from a primary data file. In LOBFILEs, LOB data instances are still considered to be in fields (predetermined size, delimited, length-value), but these fields are not organized into records (the concept of a record does not exist within LOBFILEs). Therefore, the processing overhead of dealing with records is avoided. This type of organization of data is ideal for LOB loading.

There is no requirement that a LOB from a LOBFILE fit in memory. SQL*Loader reads LOBFILEs in 64 KB chunks.

In LOBFILEs the data can be in any of the following types of fields:

  • A single LOB field into which the entire contents of a file can be read

  • Predetermined size fields (fixed-length fields)

  • Delimited fields (that is, TERMINATED BY or ENCLOSED BY)

    The clause PRESERVE BLANKS is not applicable to fields read from a LOBFILE.

  • Length-value pair fields (variable-length fields)

    To load data from this type of field, use the VARRAW, VARCHAR, or VARCHARC SQL*Loader data types.

See "Examples of Loading LOB Data from LOBFILEs" for examples of using each of these field types. All of the previously mentioned field types can be used to load XML columns.

See "lobfile_spec" for LOBFILE syntax.