Examples of Loading LOB Data from LOBFILEs

This section contains examples of loading data from different types of fields in LOBFILEs.

One LOB per File

In Example 11-18, each LOBFILE is the source of a single LOB. To load LOB data that is organized in this way, the column or field name is followed by the LOBFILE data type specifications.

Example 11-18 Loading LOB DATA with One LOB per LOBFILE

Control File Contents

LOAD DATA 
INFILE 'sample.dat'
   INTO TABLE person_table
   FIELDS TERMINATED BY ','
   (name      CHAR(20),
1  ext_fname    FILLER CHAR(40),
2  "RESUME"     LOBFILE(ext_fname) TERMINATED BY EOF)

Data File (sample.dat)

Johny Quest,jqresume.txt,
Speed Racer,'/private/sracer/srresume.txt',

Secondary Data File (jqresume.txt)

             Johny Quest
         500 Oracle Parkway
            ...

Secondary Data File (srresume.txt)

         Speed Racer
     400 Oracle Parkway
        ...

Note:

The callouts, in bold, to the left of the example correspond to the following notes:

  1. The filler field is mapped to the 40-byte data field, which is read using the SQL*Loader CHAR data type. This assumes the use of default byte-length semantics. If character-length semantics were used, then the field would be mapped to a 40-character data field

  2. SQL*Loader gets the LOBFILE name from the ext_fname filler field. It then loads the data from the LOBFILE (using the CHAR data type) from the first byte to the EOF character. If no existing LOBFILE is specified, then the "RESUME" field is initialized to empty.

Predetermined Size LOBs

In Example 11-19, you specify the size of the LOBs to be loaded into a particular column in the control file. During the load, SQL*Loader assumes that any LOB data loaded into that particular column is of the specified size. The predetermined size of the fields allows the data-parser to perform optimally. However, it is often difficult to guarantee that all LOBs are the same size.

Example 11-19 Loading LOB Data Using Predetermined Size LOBs

Control File Contents

LOAD DATA 
INFILE 'sample.dat'
INTO TABLE person_table
FIELDS TERMINATED BY ','
   (name     CHAR(20),
1  "RESUME"    LOBFILE(CONSTANT '/usr/private/jquest/jqresume.txt')
               CHAR(2000))

Data File (sample.dat)

Johny Quest,
Speed Racer,

Secondary Data File (jqresume.txt)

             Johny Quest
         500 Oracle Parkway
            ...
             Speed Racer
         400 Oracle Parkway
            ...

Note:

The callout, in bold, to the left of the example corresponds to the following note:

  1. This entry specifies that SQL*Loader load 2000 bytes of data from the jqresume.txt LOBFILE, using the CHAR data type, starting with the byte following the byte loaded last during the current loading session. This assumes the use of the default byte-length semantics. If character-length semantics were used, then SQL*Loader would load 2000 characters of data, starting from the first character after the last-loaded character. See "Character-Length Semantics".

Delimited LOBs

In Example 11-20, the LOB data instances in the LOBFILE are delimited. In this format, loading different size LOBs into the same column is not a problem. However, this added flexibility can affect performance, because SQL*Loader must scan through the data, looking for the delimiter string.

Example 11-20 Loading LOB Data Using Delimited LOBs

Control File Contents

LOAD DATA 
INFILE 'sample.dat'
INTO TABLE person_table
FIELDS TERMINATED BY ','
   (name     CHAR(20),
1  "RESUME"    LOBFILE( CONSTANT 'jqresume') CHAR(2000) 
               TERMINATED BY "<endlob>\n")

Data File (sample.dat)

Johny Quest,
Speed Racer,

Secondary Data File (jqresume.txt)

             Johny Quest
         500 Oracle Parkway
            ... <endlob>
             Speed Racer
         400 Oracle Parkway
            ... <endlob>

Note:

The callout, in bold, to the left of the example corresponds to the following note:

  1. Because a maximum length of 2000 is specified for CHAR, SQL*Loader knows what to expect as the maximum length of the field, which can result in memory usage optimization. If you choose to specify a maximum length, then you should be sure not to underestimate its value. The TERMINATED BY clause specifies the string that terminates the LOBs. Alternatively, you could use the ENCLOSED BY clause. The ENCLOSED BY clause allows a bit more flexibility as to the relative positioning of the LOBs in the LOBFILE (the LOBs in the LOBFILE need not be sequential).

Length-Value Pair Specified LOBs

In Example 11-21 each LOB in the LOBFILE is preceded by its length. You could use VARCHAR, VARCHARC, or VARRAW data types to load LOB data organized in this way.

This method of loading can provide better performance over delimited LOBs, but at the expense of some flexibility (for example, you must know the LOB length for each LOB before loading).

Example 11-21 Loading LOB Data Using Length-Value Pair Specified LOBs

Control File Contents

LOAD DATA 
INFILE 'sample.dat'
INTO TABLE person_table
FIELDS TERMINATED BY ','
   (name          CHAR(20),
1  "RESUME"       LOBFILE(CONSTANT 'jqresume') VARCHARC(4,2000))

Data File (sample.dat)

Johny Quest,
Speed Racer,

Secondary Data File (jqresume.txt)

2      0501Johny Quest
       500 Oracle Parkway
          ... 
3      0000

Note:

The callouts, in bold, to the left of the example correspond to the following notes:

  1. The entry VARCHARC(4,2000) tells SQL*Loader that the LOBs in the LOBFILE are in length-value pair format and that the first 4 bytes should be interpreted as the length. The value of 2000 tells SQL*Loader that the maximum size of the field is 2000 bytes. This assumes the use of the default byte-length semantics. If character-length semantics were used, then the first 4 characters would be interpreted as the length in characters. The maximum size of the field would be 2000 characters. See "Character-Length Semantics".

  2. The entry 0501 preceding Johny Quest tells SQL*Loader that the LOB consists of the next 501 characters.

  3. This entry specifies an empty (not null) LOB.