I’ve worked on a number of systems where images were stored in the tables of the database, rather than some sort of reference to a file system location. This approach has quite a few merits; adding and modifying images will be transactional with whatever business process is updating the database and the images will be backed up with the rest of the data.
However, quite often I see the images stored in simple BLOB data type columns. This approach works just fine until you want to know details image. An example that springs to mind was the need to know the format and dimensions of the images so they could make decisions about how the images should be displayed on a screen.
Oracle has an object type for images where this type of metadata is exposed: ORDImage. You’ll find the documentation of this object type in the Multimedia User’s Guide and Multimedia Reference. One solution to the problem of determining attributes of an image stored in a BLOB is to query the BLOB, casting it to a ORDIMage type and reading the attributes that way. Let’s take a quick look at how this is done…
For the image I’m going to use for this exercise I’m going to use the banner image from this blog; picture I took just outside of Queenstown in New Zealand. This image is a 1000 x 288 jpg image.
First we need to load the image into the database in a BLOB column of a table:
CREATE TABLE img_tab (id NUMBER (6) NOT NULL ,img BLOB) / CREATE DIRECTORY img_dir AS 'C:\oracle\images' / DECLARE l_blob BLOB; l_bfile BFILE := BFILENAME('IMG_DIR', 'cropped-queenstown_sml.jpg'); l_dest_offset INTEGER := 1; l_src_offset INTEGER := 1; BEGIN dbms_lob.createtemporary (lob_loc => l_blob ,cache => TRUE); dbms_lob.open (file_loc => l_bfile); dbms_lob.loadblobfromfile (dest_lob => l_blob ,src_bfile => l_bfile ,amount => dbms_lob.lobmaxsize ,dest_offset => l_dest_offset ,src_offset => l_src_offset); INSERT INTO img_tab (id ,img) VALUES (1 ,l_blob); COMMIT; dbms_lob.close (file_loc => l_bfile); END; /
Running the above gets us our image into a column named IMG in the table IMG_TAB. Now to see what Oracle ORDImage object type can tell us about our image. We can do this directly in SQL, without having to resort to PL/SQL (note, the second parameter of the ORDImage constructor, the “1”, instructs Oracle to process the image and obtain the metadata):
WITH imgs AS (SELECT ORDImage(img, 1) AS ord_img FROM img_tab) SELECT i.ord_img.getFileFormat() AS file_format , i.ord_img.getContentLength() AS content_length , i.ord_img.getHeight() AS height , i.ord_img.getWidth() AS width FROM imgs i
which gives us an output of:
FILE_FORMAT CONTENT_LENGTH HEIGHT WIDTH --------------- -------------- ---------- ---------- JFIF 51960 288 1000
Exactly what we wanted to know.
A word of caution though. Running a query like this against a large number of stored BLOB images is likely to be processor and IO intensive. Pick a time of day when the system has capacity.