A file is in fixed record format when all records in a data file are the same byte length. Although this format is the least flexible, it results in better performance than variable or stream format. Fixed format is also simple to specify. For example:
INFILE datafile_name "fix n"
This example specifies that SQL*Loader should interpret the particular data file as being in fixed record format where every record is
n bytes long.
Example 7-1 shows a control file that specifies a data file (
example1.dat) to be interpreted in the fixed record format. The data file in the example contains five physical records; each record has fields that contain the number and name of an employee. Each of the five records is 11 bytes long, including spaces. For the purposes of explaining this example, periods are used to represent spaces in the records, but in the actual records there would be no periods. With that in mind, the first physical record is
396,...ty,. which is exactly eleven bytes (assuming a single-byte character set). The second record is
4922,beth, followed by the newline character (
\n) which is the eleventh byte, and so on. (Newline characters are not required with the fixed record format; it is simply used here to illustrate that if used, it counts as a byte in the record length.)
Note that the length is always interpreted in bytes, even if character-length semantics are in effect for the file. This is necessary because the file could contain a mix of fields, some of which are processed with character-length semantics and others which are processed with byte-length semantics. See "Character-Length Semantics".
Example 7-1 Loading Data in Fixed Record Format
load data infile 'example1.dat' "fix 11" into table example fields terminated by ',' optionally enclosed by '"' (col1, col2)
396,...ty,.4922,beth,\n 68773,ben,. 1,.."dave", 5455,mike,.