Use SQL*Loader to load LOB

The Oracle database supports a wide variety of data formats to handle all kinds of data, including graphics, documents, audio, and multimedia. Prior to Oracle, you had to use a programmed interface, such as a customized C++ program load multimedia data into the database.Now you can use SQL*Loader to do the job for you. This lesson demonstrates how to use two of the enhancements to SQL*Loader: loading data into an object and loading data into a LOB column. The simulation walks you through creating and saving a control file and then running SQL*Loader to load the data. Five images are stored in five files, which must be loaded into the LOB columns.

The simulation showed data being loaded into an object table, -a new feature for Oracle's SQL*Loader. In addition, one of the object table's attributes was a LOB column. Load data files into LOB columns using SQL*Loader. The next lesson describes the table management enhancements that will be reviewed in the rest of the module.

SQL*Loader reads data from one or more files (or operating system equivalents of files) specified in the control file. From SQL*Loader's perspective, the data in the datafile is organized as records. A particular datafile can be in fixed record format, variable record format, or stream record format. The record format can be specified in the control file with the INFILE parameter. If no record format is specified, the default is stream record format.
Note: If data is specified inside the control file (that is, INFILE * was specified in the control file), then the data is interpreted in the stream record format with the default record terminator.

A file is in fixed record format when all records in a datafile 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 datafile as being in fixed record format where every record is n bytes long.
Example 5-3 shows a control file that specifies a datafile that should be interpreted in the fixed record format. The datafile in the example contains five physical records. Assuming that a period (.) indicates a space, the first physical record is [001,...cd,.] which is exactly eleven bytes (assuming a single-byte character set). The second record is [0002,fghi,\n] followed by the newline character (which is the eleventh byte), and so on. Note that newline characters are not required with the fixed record format. 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.

load data
infile 'example.dat' "fix 11"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1, col2)
example.dat:
001, cd, 0002,fghi,
00003,lmn,
1, "pqrs",
0005,uvwx,