Wednesday, October 05, 2005

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!

33 comments:

Robert said...

Good one! thanks Scott
Why this line thou?

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

I put a little twist on it already ;)
http://htmldb.oracle.com/pls/otn/f?p=28115:1

Scott said...

Robert,

That's some code I forgot to remove. You can omit it (as I see you already have in your example) and have the image upload "as-is" into the PHOTO column of the table.

Thanks,

- Scott -

Colin said...

Why are you using a CLOB?

Also, is there a way of reducing the size of a image file upon upload? Fro example, someone tries to upload a 600K JPEG file, and I really want a limit of 100K?

Scott said...

Colin,

CLOB, BLOB - they're all the same! :) OK, they're not, and thank for catching that - it should have read BLOB.

I don't know of any way to proactively reduce the image size via interMedia. You have to take that initial hit of uploading a 6M file first, and then immediately resize it to 100k. There may be some fancy JavaScript or VBScript to limit how big a file you can actually select, but I don't think that you can resize on the fly.

Thanks,

- Scott -

Robert said...

Scott, How would you NOT display the "delete" button when on the upload page ?

Scott said...

Robert,

You can simply delete it from the page, unless you only want to conditionally hide it.

Thanks,

- Scott -

Partha said...

Hi Scot, Seems like you have redirected your previous blog to point to this blog. Haven't you imported your articles and other stuff to this blog. I used to refer to the previous blog for a lot of articles and tips. Can you either import those in this, or don't redirect from the old one.

Thanks

Partha

Scott said...

Partha,

Sorry about that - I've changed my old blog so that it no longer auto-redirects to this one.

Thanks,

- Scott -

Venkat said...

Hi scott,
My requirement is to read a blob which actually contains a .doc file with text and pictures both combined.
How to display that in an editable area say.. an Iframe..?
Venkat

Scott said...

Venkat,

You cannot edit or display a BLOB inside a web browser if the type is not some sort of image. Your best bet is to let users open the BLOB in its native editor (Word for a .doc file).

Thanks,

- Scott -

Anonymous said...

Hi Scott! The sample app works great!
Downloaded it and created the DDL but I get an error:

ORA-06550: line 8, column 1: PLS-00201: identifier 'ORDIMAGE.PROCESS' must be declared.

I asked our DBA about interMedia, he says it is installed.

Can I just load the needed procedures?

Anonymous said...

Hi Scott,
When you select lets say 400x500 proportionate.. does it save it smaller in "file size" than if you upload it as 700x900 ?
Thanks, Bill

Scott said...

Hi Scott! The sample app works great!
Downloaded it and created the DDL but I get an error:

ORA-06550: line 8, column 1: PLS-00201: identifier 'ORDIMAGE.PROCESS' must be declared.

I asked our DBA about interMedia, he says it is installed.


You'll need to be sure that the schema which you are parsing your ApEx application as has execute on that package. Your DBA should be able to grant the appropriate privs to your schema.

Just having it installed is not enough.

Thanks,

- Scott -

Scott said...

When you select lets say 400x500 proportionate.. does it save it smaller in "file size" than if you upload it as 700x900 ?

Yes - it will reduce (or increase) the file size when changing the proportions. This is a useful function to prevent large images being uploaded.

Unfortunately, the entire image has to be initially uploaded before the size can be manipulated.

Thanks,

- Scott -

dlconsev@hacc.edu said...

almost helpful.

I have a table where the image is stored as an ordimage, not as a blob.

What do I replace the wpg_docload.download_file with ? It won't compile. Says PLS-00306: wrong number or types of arguments in call to
'DOWNLOAD_FILE'
when I changed type from blob to ordimage. Thanks

Anonymous said...

First of all, sorry about my english. I trying to process an image in a after insert trigger, the reason is because I need to reduce the size automatically when someone upload the image to de database. But I recive this error: "IMG-00730: unable to process empty image". ¿Why? ¿It is posible to process an image in a after insert trigger or not? ¿anyone knows another way to do that?. Thank you. Juan Pablo from Argentine.

Justin said...

Scott,

I have tried your process to view thumbnails in a report and for the life of me can't get the thumbnails to display.

I created the following procedure:

create or replace procedure "IMAGE"
(document_id in Number)
as
l_mime varchar2(255);
l_length number;
l_file_name varchar2(2000);
lob_loc BLOB;
begin
select mime_type, blob_content, filename, dbms_lob.getlength(blob_content)
into l_mime, lob_loc, l_file_name, l_length
from project_document where project_document_id = document_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="'||substr(l_file_name,instr(l_file_name,'/')+1)|| '"');

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

I then included your test query modified to fit my code


The images do not display just get a bunch of red x boxes. Anything I am doing wrong???

Thanks

-Justin

Scott said...

Justin,

Did you grant execute to public on your image procedure?

- Scott -

Pittman said...

You are genius!

Pittman said...

Scott,

I did forget to grant execute to public on my image procedure. My next question goes one step further. I have another procedure that creates a word document but I would like to include a call to my Images procedure in the Word Procedure. I cannot seem to get the img src right in my Word procedure. The word doc opens fine with other text but my image source seems to be screwy.

The code I have in my WORD procedure for img src is:


"#OWNER#.image?document_id=46"

---------------------
The word doc opens fine but I get a broken image displaying the following path:

file:///C:/Documents and Settings/Justin/Local Settings/Temporary Internet Files/Content.IE5/MZ47MBU9/V('OWNER').image%3Fdocument_id=46


Any suggestions or is this even possible to pull images from the database? Thanks again for all the help.

-Justin

Scott said...

I have another procedure that creates a word document but I would like to include a call to my Images procedure in the Word Procedure.

I do not think that this is possible using the methods outlined here.

Thanks,

- Scott -

Kishor said...

Excellent.

Kishor

JF à Mada said...

Hi,
First,esxcuse my english,I'm from a francophone country.It was helpful for me.
But I would like to know how to make appear the photo-name on the page 2,once I've clicked on the EDIT Icon on page 1.
Thanks.

Anonymous said...

Hi Scott, I like the example about ordimage.process but how do you get the image width for the blob?

Thanks.

Steve said...

Hey Scott, old thread but still a good one.
I'm trying to insert a blob image from a table into a pl/sql region. The image accompanies the text of a radio button. Here's what I'm doing so far:
string2 :='{input type="radio" name="aa" value="1"} '|| ltrim(a2.answer)|| '{img src= "display_thumb?p_file=a2.blob_id"/} {br /}';
htp.p(string2);
blob_id is the id# of the blob file. I'm getting a broken image symbol.
Any ideas? Thanks. Waiting for your book to arrive!
Steve

Scott said...

Steve,

When you call display_thumb, you will have to prefix that with the name of your parsing schema. In APEX, you can use #OWNER# and it will automatically be replaced.

In addition to that, you will need to grant execute on that procedure to PUBLIC, so that it can be called from the URL.

Alternatively, you can call an APEX page that has a Before Header Process on it that calls just display_image; this is likely a better implementation, as you do NOT need any extra grants for it to work.

Thanks,

- Scott -

Steve said...

You nailed it! I needed #OWNER#... and the PUBLIC grant.
Thank you. Steve

Scott said...

Glad that it worked!

- Scott -

Valeria said...

Hi Scott!
Your code is fantastic.
I have a question: I print your report with Oracle BI Publisher, but if there is some image too large the report fails! how can I avoid this?

Anonymous said...

trying to create(display) a link to BLOB on a page. But i do NOT want to use the report or form. HJEre is the discussion :http://forums.oracle.com/forums/thread.jspa?threadID=927593&tstart=0
Any ideas? thank you. Andrew

Bob C said...

dlconsev@hacc.edu:

Instead of...
DECLARE
l_blob BLOB;
BEGIN
...
wpg_docload.download_file(l_blob);
END;

Try doing this...
DECLARE
l_ordimage ORDIMAGE;
BEGIN
...
wpg_docload.download_file(l_ordimage.source.localdata);
END;

Worked for me.

Raoul said...

Very useful. Thanks Scott.

Phil said...

Awesome. Thanks. I'd bee messing around wirh ordsys.image for hours until I found your post. Your solution is a lot simpler!