Load Multiple images (BLOBs) into the Database

61508381-1ef0-48eb-abb0-a47ebc86ab39

I have a folder with several images. My database table is a simple table with 2 fields: name (char) and image (BLOB). How can I import my images located in my C disk (e.g.: C:\Users\me\images) into my table?

P.S. By using the SQL Developer I found a way to import 1 image per time, but I am looking for a solution that could import my entire folder; and when I am choosing import data from sql developer, I am getting an "There are no readers registered to the jpg type" error..

Gaz in Oz

Hi 61508381-1ef0-48eb-abb0-a47ebc86ab39,

One way would be to use Oracle sqlldr, available with Oracle client and server installs:

Create a file with your images filenames that you want to load, for example:

C:\> dir/b *.jpg *.png > files.txt
C:\> type files.txt

Oracle11g-Architecture.jpg
sql.jpg
ocr.jpg

The directory listing search for jpg and png files found 3 jpg files.

files.ctl control file referencing our list of image files in files.txt:

LOAD DATA
INFILE files.txt
APPEND
INTO TABLE images
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(
name,
image LOBFILE(name) TERMINATED BY eof
)

Load the data using sqlldr and our control file above:

C:\> sqlldr user/[email protected] control=files.ctl

The filenames and their respective image files data were loaded into the images table.

SELECT name FROM images;
NAME
------------------------------
Oracle11g-Architecture.jpg
sql.jpg
ocr.jpg

3 rows selected.

Hope that helps,

Gaz.

thatJeffSmith-Oracle

@Kris Rice-Oracle has a blog post showing how to do this in SQLcl with javascript here:

   Kris' blog: SQLCL - Blob loading ALL the files

Otherwise, SQL*Loader is probably the way to go. In fact, if you use SQLDev's Cart to unload a table with BLOBs,  you'll see the script we use to put the BLOBs back in is via SQL*Loader.