Oracle how to load a picture into a blob column using sqlldr

Question

I would like to load employee photos into the table per_images. Per_images used to have a long raw column for the image, but this has changed to a blob.

The following sql loader control file used to work with the long raw column:

options (bindsize 9529)
load data
infile 0211664.jpg "fix 9529"
append
into table PER_IMAGES
(image raw(9529),
parent_id constant 6598,
table_name constant "PER_PEOPLE_F",
image_id "PER_IMAGES_s.nextval")

where 9529 is the size of the jpg picture. How should I change the raw(9529) to instead allow loading into a blob column?

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2013-08-06T12:55:02

I usually use a different way to load BLOB data using SQL*Loader. Basically I import a text file that contains the filenames and in the control file, I then tell SQL*Loader that the actual content comes from a lobfile.

In your case this means you would need to create text file that contains (only) the filename of the jpg. The control file then should look like this:

LOAD DATA
infile data.txt
TRUNCATE
INTO TABLE per_images
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
  input_file FILLER,
  image_id "PER_IMAGES_s.nextval",  
  parent_id constant 6598,
  table_name constant "PER_PEOPLE_F",  
  image lobfile(input_file) terminated by eof
)

The input file data.txt would then look like this:

0211664.jpg

It's important that the image lobfile ... part is at the end and that any constant definition comes first in the control file.

Using this kind of approach seems much simpler to me because you do not need to know the size of the input file and you can load more than one picture with a SQL*Loader run which is most probably a lot faster if you need to load a large amount of pictures.

If you want to load more than one picture, the input file needs to contain the constant values that you supplied so far inside the control file. Take the following input file:

6598,PER_PEOPLE_F,0211664.jpg
6599,PER_PEOPLE_F,0123456.jpg
6600,PER_PEOPLE_X,0987654.jpg

Then you can load all three pictures with a single control file:

LOAD DATA
infile data.txt
TRUNCATE
INTO TABLE per_images
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
  parent_id,
  table_name,
  input_file FILLER,
  image_id "PER_IMAGES_s.nextval",  
  image lobfile(input_file) terminated by eof
)

The control file will never change, only the contents of the data.txt file.

Your original control file works for me, if the raw(9529) is removed completely:

options (bindsize 9529)
load data
infile 0211664.jpg "fix 9529"
append
into table PER_IMAGES
(
  image,
  parent_id constant 6598,
  table_name constant "PER_PEOPLE_F",
  image_id "PER_IMAGES_s.nextval"
)

Oracle how to load a picture into a blob column using sqlldr

1 Answers