LOB Data in Length-Value Pair Fields

You can use VARCHAR, VARCHARC, or VARRAW data types to load LOB data organized in length-value pair fields. This method of loading provides better performance than using delimited fields, but can reduce flexibility (for example, you must know the LOB length for each LOB before loading). Example 11-17 demonstrates loading LOB data in length-value pair fields.

Example 11-17 Loading LOB Data in Length-Value Pair Fields

Control File Contents

  LOAD DATA 
1 INFILE 'sample.dat' "str '<endrec>\n'"
  INTO TABLE person_table
  FIELDS TERMINATED BY ','
     (name       CHAR(25),
2    "RESUME"    VARCHARC(3,500))

Data File (sample.dat)

  Julia Nayer,479                Julia Nayer
                             500 Example Parkway
                             jnayer@us.example.com
                                    ... <endrec>
3    Bruce Ernst,000<endrec>

Note:

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

  1. If the backslash escape character is not supported, then the string used as a record separator in the example could be expressed in hexadecimal notation.

  2. "RESUME" is a field that corresponds to a CLOB column. In the control file, it is a VARCHARC, whose length field is 3 bytes long and whose maximum size is 500 bytes (with byte-length semantics). If character-length semantics were used, then the length would be 3 characters and the maximum size would be 500 characters. See "Character-Length Semantics".

  3. The length subfield of the VARCHARC is 0 (the value subfield is empty). Consequently, the LOB instance is initialized to empty.