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.


koloo kumo said...

Hi Joel
I was trying this approach to download any file types stored in a table but doesn't seem to work. Is it only for images how can i modify to download any types of file from my custom table...thanks

Joel R. Kallman said...

Hi Koloo,

When you say "it doesn't seem to work", what kind of issues or errors are you running into? The solution above should work for virtually any MIME type, not just images. I use it all the time for PDFs and other types of documents. About the only thing you may want to vary is the Content-Disposition - from attachment to inline.

If you can explain what's not working for you, I may be able to help better.


Sameer said...

Hi Joel,

How can I use this to download a file stored as BFILE in a table? I want this in a form (not report).



Joel R. Kallman said...

Hi Sammeer,

It should be easy. Presumably, there is a form item on the page which is for the ID of the row in the table. Just use a Display Only item, with a Source of Static Value, and make the static value something like:


See how I'm using the substitution syntax for the value of the ID on the form (P3_ID)? The purpose of the above blog post is to provide you a URL to a file in this table, and all you do is vary the input ID argument.

Note, you'll have to:

1) Change the property "Escape Special Characters" of this Display Only item to No. Otherwise, the markup you're using for this item will be scaled
2) Change the property "Session State Protection" of this Display Only item to "Restricted - May not be set from Browser". Otherwise, you're giving hackers a way to introduce arbitrary markup into your page.
3) Might as well change the "Restricted Characters" of P3_ID (or whatever your ID item is on the page) to some blacklist that prevents markup. Typically, your ID column will be numeric, so you should be as aggressive as possible in what items you restrict.

To enable what you want is easy. To properly secure it down will take the few steps I've given above.

I hope this helps.


Sameer said...

Hi Joel,

Thanks a ton for quick and detailed response. Will surely implement same. However, I have a query.

In the packaged App P-Track, download has been implemented as a single SQL query ( Page 106:P106_DOWNLOAD) as :

select apex_util.get_blob_file_src('P43_FILE_BLOB', as file_link
where project_id = :P106_PROJECT_ID and id = :P106_ID

I, with my limited knowledge (ignorance?), find this to be much simpler and easy to implement. Is this better and safer approach?

Thanks, again.


Joel R. Kallman said...

Hi Sammeer,

You can try that, if you wish. It's documented and supported. But it requires some setup on your part - I believe it assumes that you have an Automatic DML form on the table containing your BLOB column, and that the Format attribute of your BLOB column specifies it as a BLOB. The documentation on this is at:

The solution I have provided requires no setup and can work in any APEX context.

I hope this helps.


kevin zhang said...

Hi Joel:

I am using APEX 5.0.3. I have a custom table defined as below:


I'd like to build a **public** page within a given APEX application (the application itself requires authentication) to allow anyone to download file from KE_CRS_FILES table without any authentication.

What is the best way to achieve this?

Kevin Zhang

Joel R. Kallman said...

Hi Kevin,

Instead of defining this as an application-level process, you could create the On Demand process (named AJAX Callback) on a page instead. Ensure that this page is public and does not require authentication. And voila - you have a public link to your files, even though your application requires authentication.

I hope this helps.


King of the Rock said...

Dear Joel

I am trying to have Apex opens and display a specific pages let us say 3-4 pages of a PDF file, do you know how that can be done. I already have knowledge to open the file in Apex but to direct it to a specific page or select just certain pages.

Joel R. Kallman said...

Dear King,

Good question. Adobe has this explained quite clearly at, but of course, that won't work with the solution I've provided in this blog post. The additional arguments in the URL would be "consumed" by the processing of the URL of APEX itself.

I haven't tried this myself, but perhaps you could do something with an ORDS REST Service, that would hopefully *not* consume the "#page=xx" portion of the URL?

I hope this helps.


Joel R. Kallman said...

Hi King,

I proved this to myself. Using a REST Service of type Media Query, I'm able to provide a link to a PDF file and have it navigate to a specific page. As an example:

This is a RESTful Service of type Media Query, and the GET handler is the query:

select 'application/pdf', content from pdf_files where id = 21

Granted - because this is not executing in an APEX context, you wont' be able to protect it like you can using the method that I provide in the blog post above. But this is a way that you can store PDF files in BLOB columns in a table and not only serve them to a browser but also navigate to a specific page in the PDF.

I hope this helps.