PL/SQL   «Prev  Next»

Lesson 7 Inserting LOB data using SQL*Loader
ObjectiveInsert data into a LOB using SQL*Loader.

Inserting LOB data using SQL*Loader

Because LOBs can be quite large, it makes sense that SQL*Loader can load LOB data either from the main datafile (that is, inline with the rest of the data) or from one or more secondary datafiles. To load LOB data from the main datafile, use the usual SQL*Loader formats. The LOB data instances can be in predetermined size fields, delimited fields, or length-value pair fields. Consequently, it is preferable that LOB data instances remain out of line from the rest of the data. LOBFILES[1] and secondary datafiles[2] (SDFs) each provide a method to separate the lengthy data.

LOBFILES

LOBFILEs are simple datafiles that help load the LOB data. A LOBFILE does not support records within it. Within LOBFILEs, the data can be in any of the following type fields:
  1. Fixed length fields
  2. Delimited fields
  3. Length-value pair fields
  4. A single LOB field into which the entire contents of a file can be read

Secondary datafiles (SDFs)

SDFs are similar to primary datafiles in terms of records. Each record within an SDF has fields within it. The SDFs are specified on a per control file field basis. You can use the SDF keyword to specify SDFs. Either the file specification string or a filler field, which is mapped to a data field containing file specification string(s), can follow the SDF keyword. Within SDFs, the LOB data can be in the following formats:
  1. Predetermined size fields: In this format, LOB data loaded in the tables is of the same size. Because it is not possible to load data of the same size to all the rows in a table, a workaround is to trim or pad the data with blank spaces to make it all the same size.
  2. Delimited fields: In this format, a certain character for loading into the object table delimits the data.
  3. Length-value pair fields: You could use VARCHAR, VARCHARC, or VARRAW data types to load LOBdata.
  4. One LOB per file: Each LOBFILE clause is the source of just one LOB. To load the LOB data organized in this manner into the control file, follow the column/field name with the LOBFILE specification and the data type specification.

Object Oriented Programming
The following example illustrates loading LOBs, with one LOB per file: 
CONTROL FILE (cpot.ctl)
load data
infile 'cpot.dat'
into table CUSTOMER_PHOTO_OBJ_TABLE
append
fields terminated by ','
(
CUSTOMER_ID integer external,
CREATED_DATE date 'dd-mon-yyyy',
cpot_file FILLER char(40),
CUSTOMER_PHOTO LOBFILE(cpot_file) 
TERMINATED BY EOF 
)

The following details should be passed when executing SQL*Loader:
  1. USERID/ password: The USERID and password
  2. Control filename: Name of the control file, which defines the table and column names for loading the data
  3. Log filename: The filename that maintains a log of the loading of data
  4. Bad filename: The name of the output file for the bad records that could not be loaded into the table
  5. Data filename: The datafile
Now that you have learned how to load LOB data using SQL*Loader, try it with the following tutor-based exercise. In this exercise, you are required to load the LOB data using SQL*Loader.
In the next lesson, we will look at updating LOB data using DBMS_LOB.

Inserting lob Data SQL Loader-Exercise

Click the Exercise link below to load the LOB data using SQL*Loader.
Inserting lob Data SQL Loader-Exercise
[1]LOBFiles: LOBFILES are relatively simple datafiles that facilitate LOB loading. The attribute that distinguishes LOBFILEs from the primary datafiles is that there is no concept of a record within LOBFILES.
[2]Secondary Data Files: Secondary-Data-File are files similar in concept to the primary datafiles. Like primary datafiles, SDFs are a collection of records and each record is made up of fields. The SDFs are specified on a per control-file-field basis.