How To Load LOB Data Using SQL*Loader And LOBFILE Feature (Doc ID 1456882.1)

Goal

This document addresses the topic of how to load LOB Data from LOBFILEs and provides an example.


Loading LOBs

A LOB is a large object type. SQL*Loader supports the following types of LOBs:

    BLOB: an internal LOB containing unstructured binary data
    CLOB: an internal LOB containing character data
    NCLOB: an internal LOB containing characters from a national character set
    BFILE: a BLOB stored outside of the database tablespaces in a server-side operating system file

LOBs can be column datatypes, and with the exception of the NCLOB, they can be an object's attribute datatypes. LOBs can have actual values, they can be null, or they can be empty. SQL*Loader creates an empty LOB when there is a 0-length field to store in the LOB. (Note that this is different than other datatypes where SQL*Loader sets the column to NULL for any 0-length string.) This means that the only way to load NULL values into a LOB column is to use the NULLIF clause.

XML columns are columns declared to be of type SYS.XMLTYPE. SQL*Loader treats XML columns as if they were CLOBs. All of the methods described in the following sections for loading LOB data from the primary datafile or from LOBFILEs are applicable to loading XML columns.

Note:
You cannot specify a SQL string for LOB fields. This is true even if you specify LOBFILE_spec.

Because LOBs can be quite large, SQL*Loader is able to load LOB data from either a primary datafile (in line with the rest of the data) or from LOBFILEs. This document demonstrates how to load LOB Data from a LOBFILE.


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 datafile. 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:

1. A single LOB field into which the entire contents of a file can be read
    
2. Predetermined size fields (fixed-length fields)
    
3. Delimited fields (that is, TERMINATED BY or ENCLOSED BY)

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

4. Length-value pair fields (variable-length fields)

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


Dynamic Versus Static LOBFILE Specifications

You can specify LOBFILEs either statically (the name of the file is specified in the control file) or dynamically (a FILLER field is used as the source of the filename). In either case, if the LOBFILE is not terminated by EOF, then when the end of the LOBFILE is reached, the file is closed and further attempts to read data from that file produce results equivalent to reading data from an empty field.

However, if you have a LOBFILE that is terminated by EOF, then the entire file is always returned on each attempt to read data from that file.

You should not specify the same LOBFILE as the source of two different fields. If you do so, typically, the two fields will read the data independently.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.