This section contains examples of loading data from different types of fields in LOBFILEs.
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 ...
The callouts, in bold, to the left of the example correspond to the following notes:
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
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.
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 ...
The callout, in bold, to the left of the example corresponds to the following note:
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".
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>
The callout, in bold, to the left of the example corresponds to the following note:
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).
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
The callouts, in bold, to the left of the example correspond to the following notes:
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".
The entry 0501
preceding Johny
Quest
tells SQL*Loader that the LOB consists of the next 501 characters.
This entry specifies an empty (not null) LOB.