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>
The callouts, in bold, to the left of the example correspond to the following notes:
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.
"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".
The length subfield of the VARCHARC
is 0 (the value subfield is empty). Consequently, the LOB instance is initialized to empty.