Skip to main content

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!

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 -
Anonymous 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 -
Anonymous 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 -
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 -
Anonymous 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 -
Justin 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 -
Unknown 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.
Anonymous 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 -
Anonymous 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!
Cristina said…
Loved it! Great post!

Popular posts from this blog

Custom Export to CSV

It's been a while since I've updated my blog. I've been quite busy lately, and just have not had the time that I used to. We're expecting our 1st child in just a few short weeks now, so most of my free time has been spent learning Lamaze breathing, making the weekly run to Babies R Us, and relocating my office from the larger room upstairs to the smaller one downstairs - which I do happen to like MUCH more than I had anticipated. I have everything I need within a short walk - a bathroom, beer fridge, and 52" HD TV. I only need to go upstairs to eat and sleep now, but alas, this will all change soon... Recently, I was asked if you could change the way Export to CSV in ApEx works. The short answer is, of course, no. But it's not too difficult to "roll your own" CSV export procedure. Why would you want to do this? Well, the customer's requirement was to manipulate some data when the Export link was clicked, and then export it to CSV in a forma

Refreshing PL/SQL Regions in APEX

If you've been using APEX long enough, you've probably used a PL/SQL Region to render some sort of HTML that the APEX built-in components simply can't handle. Perhaps a complex chart or region that has a lot of custom content and/or layout. While best practices may be to use an APEX component, or if not, build a plugin, we all know that sometimes reality doesn't give us that kind of time or flexibility. While the PL/SQL Region is quite powerful, it still lacks a key feature: the ability to be refreshed by a Dynamic Action. This is true even in APEX 5. Fortunately, there's a simple workaround that only requires a small change to your code: change your procedure to a function and call it from a Classic Report region. In changing your procedure to a function, you'll likely only need to make one type of change: converting and htp.prn calls to instead populate and return a variable at the end of the function. Most, if not all of the rest of the code can rem

Logging APEX Report Downloads

A customer recently asked how APEX could track who clicked “download” from an Interactive Grid.  After some quick searching of the logs, I realized that APEX simply does not record this type of activity, aside from a simple page view type of “AJAX” entry.  This was not specific enough, and of course, led to the next question - can we prevent users from downloading data from a grid entirely? I knew that any Javascript-based solution would fall short of their security requirements, since it is trivial to reconstruct the URL pattern required to initiate a download, even if the Javascript had removed the option from the menu.  Thus, I had to consider a PL/SQL-based approach - one that could not be bypassed by a malicious end user. To solve this problem, I turned to APEX’s Initialization PL/SQL Code parameter.  Any PL/SQL code entered in this region will be executed before any other APEX-related process.  Thus, it is literally the first place that a developer can interact with an APEX p