Example 11-2 shows a case in which the data is in delimited fields.
Example 11-2 Loading Column Objects in Variable Record Format
Control File Contents
LOAD DATA 1 INFILE 'sample.dat' "var 6" INTO TABLE departments FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 2 (dept_no dept_name, dept_mgr COLUMN OBJECT (name CHAR(30), age INTEGER EXTERNAL(5), emp_id INTEGER EXTERNAL(5)) )
Data File (sample.dat)
3 000034101,Mathematics,Johny Q.,30,1024, 000039237,Physics,"Albert Einstein",65,0000,
The callouts, in bold, to the left of the example correspond to the following notes:
"var" string includes the number of bytes in the length field at the beginning of each record (in this example, the number is 6). If no value is specified, then the default is 5 bytes. The maximum size of a variable record is 2^32-1. Specifying larger values will result in an error.
Although no positional specifications are given, the general syntax remains the same (the column object's name followed by the list of its attributes enclosed in parentheses). Also note that an omitted type specification defaults to
CHAR of length 255.
The first 6 bytes (italicized) specify the length of the forthcoming record. These length specifications include the newline characters, which are ignored thanks to the terminators after the