Managing LOBs , 7 of 12

Loading Inline LOB Data

Loading Inline LOB Data in Predetermined Size Fields

This is a very fast and simple way to load LOBs. Unfortunately, the LOBs to be loaded are not usually the same size.

A possible work-around is to pad LOB data with white space to make all LOBs the same length within the particular datafield; for information on trimming of trailing white spaces see "Trimming of Blanks and Tabs" in Oracle9i Utilities).

To load LOBs using this format, use either CHAR or RAW as the loading datatype. For example:

Control File

LOAD DATA 
INFILE 'sample.dat' "fix 21" 
INTO TABLE Multimedia_tab 
    (Clip_ID POSITION(1:3) INTEGER EXTERNAL, 
    Story POSITION(5:20)   CHAR DEFAULTIF Story=BLANKS)

Data File (sample.dat)

007 Once upon a time

Note:

One space separates the Clip_ID,(O07) from the beginning of the story. The story is 15 bytes long.

If the datafield containing the story is empty, then an empty LOB instead of a NULL LOB is produced. A NULL LOB is produced only if the NULLIF clause is used instead of the DEFAULTIF clause. You can use loader datatypes other than CHAR to load LOBs. Use the RAW datatype when loading BLOBs.

Note:

You can specify both NULLIF and DEFAULTIF for the same field, although NULLIF has a higher 'priority' than DEFAULTIF.

Loading Inline LOB Data in Delimited Fields

Loading different size LOBs in the same column (that is, datafile field) 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 'sample1.dat' "str '<endrec>\n'"
INTO TABLE Multimedia_tab
FIELDS TERMINATED BY ','
(
Clip_ID   CHAR(3),
 Story    CHAR(507) ENCLOSED BY '<startlob>' AND '<endlob>'
)

Data File (sample1.dat)

007,    <startlob>     Once upon a time,The end.     <endlob>|
008,    <startlob>     Once upon another time ....The end.     <endlob>|

Loading Inline LOB Data in Length-Value Pair Fields

You could use VARCHAR (see Oracle9i Utilities), VARCHARC, or VARRAW datatypes to load LOB data organized in this way. Note that this method of loading produces better performance over the previous method, however, it removes some of the flexibility, that is, it requires you to know the LOB length for each LOB before loading. For example:

Control File

LOAD DATA
INFILE 'sample2.dat' "str '<endrec>\n'"
INTO TABLE Multimedia_tab
FIELDS TERMINATED BY ','
(
Clip_ID  INTEGER EXTERNAL (3),
 Story   VARCHARC (3, 500)
)

Data File (sample2.dat)

007,041    Once upon a time...  ....  The end.  <endrec>
008,000 <endrec>

Note:

Story is a field corresponding to a CLOB column. In the control file, it is described as a VARCHARC (3, 500) whose length field is 3 bytes long and maximum size is 500 bytes. This tells the Loader that it can find the length of the LOB data in the first 3 bytes.
The length subfield of the VARCHARC is 0 (that is, the value subfield is empty); consequently, the LOB instance is initialized to empty.
Make sure the last character of the last line of the data file above is a line feed.