Managing LOBs , 8 of 12

Loading Out-Of-Line LOB Data

This section describes the following topics:

As mentioned earlier, LOB data can be so large that it is reasonable to want to load it from secondary datafile(s).

In LOBFILEs, LOB data instances are still thought 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); thus, the processing overhead of dealing with records is avoided. This type of organization of data is ideal for LOB loading.

Loading One LOB Per File

Each LOBFILE contains a single LOB. For example:

Control File

LOAD DATA
INFILE 'sample3.dat'
INTO TABLE Multimedia_tab
REPLACE
FIELDS TERMINATED BY ','
(
 Clip_ID        INTEGER EXTERNAL(5),
 ext_FileName   FILLER CHAR(40),
 Story          LOBFILE(ext_FileName) TERMINATED BY EOF
)

Data File (sample3.dat)

007,FirstStory.txt,
008,/tmp/SecondStory.txt,

Secondary Data File (FirstStory.txt)

Once upon a time ...
The end.

Secondary Data File (SecondStory.txt)

Once upon another time ....
The end.

Note::

STORY tells the Loader that it can find the LOB data in the file whose name is stored in the ext_FileName field.
TERMINATED BY EOF tells the Loader that the LOB will span the entire file.
See also Oracle9i Utilities

Loading Out-of-Line LOB Data in Predetermined Size Fields

In the control file, the size of the LOBs to be loaded into a particular column is specified. During the load, any LOB data loaded into that column is assumed to be the specified size. The predetermined size of the fields allows the dataparser to perform very well. Unfortunately, it is often hard to guarantee that all the LOBs are the same size. For example:

Control File

LOAD DATA 
INFILE 'sample4.dat'
INTO TABLE Multimedia_tab
FIELDS TERMINATED BY ','
(
 Clip_ID    INTEGER EXTERNAL(5),
 Story      LOBFILE (CONSTANT 'FirstStory1.txt') CHAR(32)
)

Data File (sample4.dat)

007,
008,

Secondary Data File (FirstStory1.txt)

Once upon the time ...
The end,
Upon another time ...
The end,

Note::

SQL *Loader loads 2000 bytes of data from the FirstStory.txt LOBFILE, using CHAR datatype, starting with the byte following the byte loaded last during the current loading session.

Loading Out-of-Line LOB Data in Delimited Fields

LOB data instances in LOBFILE files are delimited. In this format, loading different size LOBs into the same column is not a problem. The trade-off for this added flexibility is performance. Loading in this format is somewhat slower because the loader has to scan through the data, looking for the delimiter string. For example:

Control File

LOAD DATA
INFILE 'sample5.dat'
INTO TABLE Multimedia_tab
FIELDS  TERMINATED BY ','
(Clip_ID    INTEGER EXTERNAL(5),
Story       LOBFILE (CONSTANT 'FirstStory2.txt') CHAR(2000)
TERMINATED BY "<endlob>")

Data File (sample5.dat)

007,
008,

Secondary Data File (FirstStory2.txt)

Once upon a time...
The end.<endlob>
Once upon another time...
The end.<endlob>

Note:

The TERMINATED BY clause specifies the string that terminates the LOBs.

Loading Out-of-Line LOB Data in Length-Value Pair Fields

Each LOB in the LOBFILE is preceded by its length. You can use VARCHAR (see Oracle8 Utilities), VARCHARC, or VARRAW datatypes to load LOB data organized in this way. The controllable syntax for loading length-value pair specified LOBs is quite simple.

Note that this method of loading performs better than the previous one, but at the same time it takes some of the flexibility away, that is, it requires that you know the length of each LOB before loading. For example:

Control File

LOAD DATA
INFILE 'sample6.dat'
INTO TABLE Multimedia_tab
FIELDS TERMINATED BY ','
(
Clip_ID     INTEGER EXTERNAL(5),
Story       LOBFILE (CONSTANT 'FirstStory3.txt') VARCHARC(4,2000)
)

Data File (sample6.dat)

007,
008,

Secondary Data File (FirstStory3.txt)

0031
Once upon a time ... The end.
0000

Note:

VARCHARC(4,2000) tells the loader that the LOBs in the LOBFILE are in length-value pair format and that the first four bytes should be interpreted as length. The max_length part (that is, 2000) gives the hint to the loader as to the maximum size of the field.

0031 tells the loader that the next 31 bytes belong to the specified LOB.
0000 results in an empty LOB (not a NULL LOB).