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

2 comments:

  1. Excellent post..I guess the same example can be followed to load XMLTYPE data using SQL Loader into a table that has XMLTYPE columns..thanks for this post.

    ReplyDelete
  2. That was very helpfull! Thanks a lot,
    how can i add more Files when i need to?

    ReplyDelete