Thursday, March 20, 2014

Yet Another Post How to Link to Download a File or Display an Image from a BLOB column

On an internal mailing list, an employee (Richard, a long-time user of Oracle Application Express) asked:

"...we are attempting to move to storing (the images) in a BLOB column in our own application tables.  Is there no way to display an image outside of page items and reports? "

Basically, he has a bunch of images stored in the BLOB column of the common upload table, APEX_APPLICATION_FILES (or WWV_FLOW_FILES).  He wishes to move them to a table in his workspace schema, but it's unclear to him how they can be displayed.  While there is declarative support for BLOBs in Application Express, there are times where you simply wish to get a link which would return the image - and without having to add a form and report against the table containing the images.

I fully realize that this question has been answered numerous times in various books and blog posts, but I wish to reiterate it here again.

Firstly, a way not to do this is via a PL/SQL procedure that is called directly from a URL.  I see this "solution" commonly documented on the Internet, and in general, it should not be followed.  The default configuration of Oracle Application Express has a white list of entry points, callable from a URL.  For security reasons, you absolutely want to leave this restriction in place and not relax it.  This is specified as the PlsqlRequestValidationFunction for mod_plsql and security.disableDefaultExclusionList for Oracle REST Data Services (nee APEX Listener).  With this default security measure in place, you will not be able to invoke a procedure in your schema from a URL.  Good!

The easiest way to return an image from a URL in an APEX application is either via a RESTful Service or via an On-Demand process.  This blog post will cover the On-Demand process.  It's definitely easier to implement via a RESTful Service, and if you can do it via a RESTful call, that will always be much faster - Kris has a great example how to do this. However, one benefit of doing this via an On Demand process is that it will also be constrained by any conditions or authorization schemes that are in place for your APEX application (that is, if your application requires authentication and authorization, someone won't be able to access the URL unless they are likewise authenticated to your APEX application and fully authorized).

  1. Navigate to Application Builder -> Shared Components -> Application Items
  2. Click Create
    • Name:  FILE_ID
    • Scope:  Application
    • Session State Protection:  Unrestricted
  3. Navigate to Application Builder -> Shared Components -> Application Processes
  4. Click Create
    • Name: GETIMAGE
    • Point:  On Demand: Run this application process when requested by a page process.
  5. Click Next
  6. For Process Text, enter the following code:

    for c1 in (select *
                 from my_image_table
                where id = :FILE_ID) loop
        sys.owa_util.mime_header( c1.mime_type, FALSE );
        sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( c1.blob_content));
        sys.htp.p('Content-Disposition: attachment; filename="' || c1.filename || '"' );
        sys.htp.p('Cache-Control: max-age=3600');  -- tell the browser to cache for one hour, adjust as necessary
        sys.wpg_docload.download_file( c1.blob_content );
    end loop;

Then, all you need to do is construct a URL in your application which calls this application process, as described in the Application Express Application Builder Users' Guide.  You could manually construct a URL using APEX_UTIL.PREPARE_URL, or specify a link in the declarative attributes of a Report Column.  Just be sure to specify a Request of 'APPLICATION_PROCESS=GETIMAGE' (or whatever your application process name is).  The URL will look something like:


That's all there is to it.

A few closing comments:
  1. Be mindful of the authorization scheme specified for the application process.  By default, the Authorization Scheme will be "Must Not Be Public User", which is normally acceptable for applications requiring authentication.  But also remember that you could restrict these links based upon other authorization schemes too.
  2. If you want to display the image inline instead of being downloaded by a browser, just change the Content-Disposition from 'attachment' to 'inline'.
  3. A reasonable extension and optimization to this code would be to add a version number to your underlying table, increment it every time the file changes, and then reference this file version number in the URL.  Doing this, in combination with a Cache-Control directive in the MIME header would let the client browser cache it for a long time without ever running your On Demand Process again (and thus, saving your valuable database cycles).
  4. Application Processes can also be defined on the page-level, so if you wished to have the download link be constrained by the authorization scheme on a specific page, you could do this too.
  5. Be careful how this is used. If you don't implement some form of browser caching, then a report which displays 500 images inline on a page will result in 500 requests to the APEX engine and database, per user per page view! Ouch! And then it's a matter of time before a DBA starts hunting for the person slamming their database and reports that "APEX is killing our database". There is an excellent explanation of cache headers here.


Steve Maxwell said...

Hello Joel, pretty sound advise. Is there anything wrong with using a DISPLAY IMAGE in a form that is based on a BLOB column returned by a SQL query?

Joel R. Kallman said...

Hi Steve,

No - there is definitely nothing wrong with that approach.


Steve W said...

Spent almost a whole day trying to figure this out, found this page and within 5 minutes I had it. Awesome. Pure awesome.

Shubhanshu Rawat said...

Hello Joel,

I created the above mentioned process as dynamic PL/SQL content.
Can we align image anywhere in page?
So when I am running the page its only showing image, other page regions are not showing.

Please help me out for this.


Joel R. Kallman said...


Adding this block as dynamic content won't really work. Your page (or even the region in your page) needs to be able to reference the image via some URL in an IMG tag. You still need to define this as an On Demand application-level or page-level process, and then reference it somewhere via an IMG tag. That IMG tag could be generated dynamically in a region with Dynamic PL/SQL content, if you wished.

I hope this helps.


Joe Upshaw said...

As the others have said here, Joel...lot's of confusing (and plain wrong) approaches out there. I burned through a lot of development cycles before finding this one after which I had it working in 10 minutes. THANK YOU.

I am wondering, under Chrome, is it possible to get the pdf to display within a container on the page? I do have the pdf displaying inline now (thanks!) However, when the code executes, the pdf is displayed as a full page. I really need for it to be displayed within a region on the page as there are other controls present that the end users need to use. I tried adding iframe around the pdf content but, this appears to be ignored.


OWA_UTIL.MIME_HEADER( 'application/pdf', FALSE );

HTP.P( '' );
HTP.P( 'Content-length: ' || ln_PDFBlobLength );
HTP.P( 'Content-Disposition: inline; filename="' || ls_DefaultDownloadFileName || '"' );
HTP.P( 'Cache-Control: max-age=3600');
HTP.P( '' );


WPG_DOCLOAD.DOWNLOAD_FILE( lblb_MemberLetterPDFContent );


Any ideas how to get the pdf displayed within a container on the page?

Joel R. Kallman said...

Hi Joe,

I'm not sure if this works across all browsers, but you should be able to do something *like* the following with an EMBED tag:

<embed src="f?p=&APP_ID.:0:&APP_SESSION.:APPLICATION_PROCESS=GETIMAGE:::FILE_ID:1234" width="600" height="800" pluginspage="">

And, as you already pointed out, the Content-Disposition will need to be inline.

I hope this helps.


Alix Janeth Jerez Q. said...

Good morning Joel:

I have a problem and i cant get that it works

- I created applications items and the application process according your post
- I created this pl/sql region

l_url varchar2(2000);
l_app number := v('APP_ID');
l_session number := v('APP_SESSION');

for rec in (select mimedoc, iddoc, description
from documents

IF rec.mimedoc is not null or rec.mimedoc != '' THEN

p_url => 'f?p=' || l_app || ':0:'|| l_session
p_checksum_type => 'SESSION');


htp.p( 'No Image ');
htp.p( '
end loop;

- when I execute my page, the result is

- I can't see images, only I see the link

I appreciate a lot your help.

Where is the problem ?.


Joel R. Kallman said...

Hi Alex,

I think you need to actually reference the URL in an IMG tag. As an example:

sys.htp.p('<img src="' || l.url || '">');


Alix Janeth Jerez Q. said...

Hi Joel:

I'm very thankful for your help. It works !!!

Do you have any document/link about display database images like slides ?


Joel R. Kallman said...

Hi Alix (sorry about the typo earlier),

I'm sorry - I'm not sure what you mean about "images like slides". Are you referring to a carousel? Either way, this isn't really an APEX issue, but really just an HTML question.


Alix Janeth Jerez Q. said...

Hi Joel:

Yes, I mean a carrousel. I know it's html / js / ajax
Do you have information about how I do this ?

Thanks for your valuable assistance.


Joel R. Kallman said...

Hi Alix,

You want to take a look at the Carousel template type, in APEX 5 and the Universal Theme. An overview of the Universal Theme is at

I hope this helps.


jeanmarc said...

hi joel,
do you know a way to download multiple files at once ?
not possible ?

Joel R. Kallman said...

Hi Jean Marc,

Maybe you can use something like APEX_ZIP (new in APEX 5.0) to zip up the contents of several files from BLOBs and then download the zip.