Manipulating Images with the... Database?

A recent thread on the OTN HTML DB Forum asked about how to determine the width & height of an image stored as a BLOB in an Oracle table. I mentioned in that thread that I have some code to manipulate an image stored in a BLOB column. This is particularly useful if you’re going to let users upload images, and you want to re-size them to display as a thumbnail.

Thanks to Oracle interMedia, it is trivial to manipulate the width, height, and other attributes of images stored in an Oracle table. I’ve created a sample application here which demonstrates Oracle interMedia and HTML DB in action. Feel free to have a look. You can download this application from HTML DB Studio as well.

Basically, this application allows you to upload images and perform an operation on the image as it is inserted into the PHOTO_CATALOG table. There are two places where some PL/SQL code is required: an After Submit process on page 2, and a procedure to display the images.

Here is the PL/SQL for the After Submit process with some comments in-line:

declare
l_photo blob;
l_thumb blob;
begin
-- Fetch the 2 BLOB columns into local variables
select blob_content a, blob_content b into l_photo, l_thumb
from wwv_flow_files where name = :P2_PHOTO_NAME;

-- Rezise the PHOTO column so that it is proportionally 400x400
ordimage.process(l_photo, 'maxScale=400 400');

-- Determine which operation was selected, and then perform
-- the appropriate Oracle interMedia function on that image,
-- using the user defined width & height
if :P2_OPERATION = 'Proportional' then
ordimage.process(l_thumb, 'maxScale=&P2_WIDTH. &P2_HEIGHT.');
elsif :P2_OPERATION = 'Fixed' then
ordimage.process(l_thumb, 'fixedScale=&P2_WIDTH. &P2_HEIGHT.');
elsif :P2_OPERATION = 'Flip' then
ordimage.process(l_thumb, 'flip');
elsif :P2_OPERATION = 'Mirror' then
ordimage.process(l_thumb, 'mirror');
else
null;
end if;

-- Insert the data into the PHOTO_CATALOG table as a SELECT from
-- WWV_FLOW_FILES and by referencing HTML DB items
insert into photo_catalog
(photo_catalog_id, photo_name, description, uploaded_on,
mime_type, photo, thumbnail)
select :P2_PHOTO_CATALOG_ID, :P2_PHOTO_NAME, :P2_DESCRIPTION,
sysdate, mime_type, l_photo, l_thumb
from wwv_flow_files where name = :P2_PHOTO_NAME;

-- Remove the image from the HTML DB wwv_flows_files table
delete from wwv_flow_files where name = :P2_PHOTO_NAME;
end;

The code for the procedure used to display the image is similar to the CUSTOM_IMAGE_DISPLAY procedure code supplied as a part of the Sample Application.

create or replace procedure display_thumb (p_photo_id in number)
as
l_mime varchar2(255);
l_length number;
l_file_name varchar2(2000);
lob_loc BLOB;
begin
select mime_type, thumbnail, photo_name, dbms_lob.getlength(thumbnail)
into l_mime, lob_loc, l_file_name, l_length
from photo_catalog where photo_catalog_id = p_photo_id;

-- Set up HTTP header
-- Use an NVL around the mime type and if it is a null, set it to
-- application/octect - which may launch a download window from windows
owa_util.mime_header(nvl(l_mime,'application/octet'), FALSE );

-- Set the size so the browser knows how much to download
htp.p('Content-length: ' || l_length);

-- The filename will be used by the browser if the users does a "Save as"
htp.p('Content-Disposition: filename="' || l_file_name || '"');

-- Close the headersowa_util.http_header_close;
-- Download the BLOB
wpg_docload.download_file( Lob_loc );
end;

Finally, the query on Page 1 has some HTML embedded into it so that it can display the THUMBNAIL column in the report. Here’s the SQL for that query:

select "PHOTO_CATALOG_ID", "PHOTO_NAME", "DESCRIPTION", "UPLOADED_ON",
"MIME_TYPE", '<img src="#OWNER#.display_thumb?p_photo_id=' ||
nvl(photo_catalog_id,0) || '" />' thumbnail
from "PHOTO_CATALOG"

That’s all you need in order to do some basic image manipulation with Oracle interMedia and HTML DB!