SQL Loader - Load BLOB data into a table

Loading blob data into an Oracle table is always a challenge, and that too when we do not have access to the server file system, creating directory objects is just not possible.

SQL loader is a handy tool in this case, using which we will be able to push an image directly to a table in Oracle. Following article explains how to do that with a sample table:-

Let us first create a table which will hold our image data:-

CREATE TABLE image_table (
image_id NUMBER(5),
file_name VARCHAR2(30),
image_data BLOB)

We need three image files as a sample, let us call it

img1.jpg

img2.jpg

img3.jpg

The control file for SQL loader looks like the one provided below

LOAD DATA

INFILE *

INTO TABLE image_table

REPLACE

FIELDS TERMINATED BY ','

(

image_id,

file_name,

image_data LOBFILE (file_name) TERMINATED BY EOF

)

BEGINDATA

001,img1.jpg

002,img2.jpg

003,img3.jpg

When this control file is executed in SQL loader with the following syntax (control file and image file should be in same directory, for this example)

D:\sqlldr>sqlldr userid/pwd@database control=loadct.ctl

The image gets pushed into the table!..The SQL loader log file is provided below

Control File: loadct.ctl

Data File: loadct.ctl

Bad File: loadct.bad

Discard File: none specified

(Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Bind array: 64 rows, maximum of 256000 bytes

Continuation: none specified

Path used: Conventional

Table IMAGE_TABLE, loaded from every logical record.

Insert option in effect for this table: REPLACE

Column Name Position Len Term Encl Datatype

------------------------------ ---------- ----- ---- ---- ---------------------

IMAGE_ID FIRST * , CHARACTER

FILE_NAME NEXT * , CHARACTER

IMAGE_DATA DERIVED * EOF CHARACTER

Dynamic LOBFILE. Filename in field FILE_NAME

Table IMAGE_TABLE:

3 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Space allocated for bind array: 33024 bytes(64 rows)

Read buffer bytes: 1048576

Total logical records skipped: 0

Total logical records read: 3

Total logical records rejected: 0

Total logical records discarded: 0

Elapsed time was: 00:00:00.80

CPU time was: 00:00:00.16