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:

begin
    for c1 in (select *
                 from my_image_table
                where id = :FILE_ID) loop
        --
        sys.htp.init;
        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.owa_util.http_header_close;
        sys.wpg_docload.download_file( c1.blob_content );
    
        apex_application.stop_apex_engine;
    end loop;
end;

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:

f?p=&APP_ID.:0:&APP_SESSION.:APPLICATION_PROCESS=GETIMAGE:::FILE_ID:<some_valid_id>

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.

32 comments:

  1. 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?

    ReplyDelete
  2. Hi Steve,

    No - there is definitely nothing wrong with that approach.

    Joel

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

    ReplyDelete
  4. 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.

    Thanks

    ReplyDelete
  5. Shubhanshu,

    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.

    Joel

    ReplyDelete
  6. 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.

    HTP.INIT;

    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( '' );

    OWA_UTIL.HTTP_HEADER_CLOSE;

    WPG_DOCLOAD.DOWNLOAD_FILE( lblb_MemberLetterPDFContent );

    APEX_APPLICATION.STOP_APEX_ENGINE;

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

    ReplyDelete
  7. 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="http://www.adobe.com/products/acrobat/readstep2.html">

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

    I hope this helps.

    Joel

    ReplyDelete
  8. 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

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

    begin
    for rec in (select mimedoc, iddoc, description
    from documents
    )
    loop

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

    l_url := APEX_UTIL.PREPARE_URL(
    p_url => 'f?p=' || l_app || ':0:'|| l_session
    ||':APPLICATION_PROCESS=GETIMAGE:::IMAGE_ID:' || iddoc,
    p_checksum_type => 'SESSION');

    htp.p(l_url);

    else
    htp.p( 'No Image ');
    END IF;
    htp.p( '
    ');
    end loop;
    end;

    - when I execute my page, the result is
    f?p=500:0:12104678749262:APPLICATION_PROCESS=GETIMAGE:::IMAGE_ID:1

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

    I appreciate a lot your help.

    Where is the problem ?.

    Thanks.

    ReplyDelete
  9. 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 || '">');

    Joel

    ReplyDelete
  10. Hi Joel:


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

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


    Thanks,

    ReplyDelete
  11. 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.

    Joel

    ReplyDelete
  12. 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.

    Alix.

    ReplyDelete
  13. 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 https://apex.oracle.com/ut.

    I hope this helps.

    Joel

    ReplyDelete
  14. hi joel,
    do you know a way to download multiple files at once ?
    not possible ?
    regards
    jm

    ReplyDelete
  15. 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.

    Joel

    ReplyDelete
  16. 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

    ReplyDelete
  17. 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.

    Joel

    ReplyDelete
  18. 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).

    Thx

    Sammeer

    ReplyDelete
  19. 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:

    <a href="f?p=&APP_ID.:0:&APP_SESSION.:APPLICATION_PROCESS=GETIMAGE:::FILE_ID:&P3_ID.">Download</a>

    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.

    Joel

    ReplyDelete
  20. 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',f.id) as file_link
    from EBA_PROJ_STATUS_FILES f
    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.

    Sammeer

    ReplyDelete
  21. 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: https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_util.htm#AEAPI129

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

    I hope this helps.

    Joel

    ReplyDelete
  22. Hi Joel:

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

    CREATE TABLE KE_CRS_FILES
    (
    ID NUMBER,
    ROW_VERSION_NUMBER NUMBER,
    CRS_ID NUMBER NOT NULL,
    DOCUMENT_TYPE VARCHAR2(35 BYTE) NOT NULL,
    DOCUMENT_CATEGORY VARCHAR2(60 BYTE) NOT NULL,
    RELEASE_TO_CUSTOMER VARCHAR2(1 BYTE) NOT NULL,
    STATUS VARCHAR2(1 BYTE),
    FILENAME VARCHAR2(512 BYTE),
    FILE_MIMETYPE VARCHAR2(512 BYTE),
    FILE_CHARSET VARCHAR2(512 BYTE),
    FILE_BLOB BLOB,
    FILE_COMMENTS VARCHAR2(4000 BYTE),
    CREATED TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
    CREATED_BY VARCHAR2(255 BYTE) NOT NULL,
    UPDATED TIMESTAMP(6) WITH LOCAL TIME ZONE,
    UPDATED_BY VARCHAR2(255 BYTE)
    )

    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?
    Thanks!

    Kevin Zhang

    ReplyDelete
  23. 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.

    Joel

    ReplyDelete
  24. 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.

    ReplyDelete
  25. Dear King,

    Good question. Adobe has this explained quite clearly at https://helpx.adobe.com/acrobat/kb/link-html-pdf-page-acrobat.html, 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

    ReplyDelete
  26. 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:

    https://apex.oracle.com/pls/apex/apex_otn/joel_demo/pdf_file#page=3

    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.

    Joel

    ReplyDelete
  27. Hi Joel,
    I followed your codes and was able to browse/download a pdf file in apex application (4.2.0.00.27) beautifully. However, I have problem downloading multiple pdf files in one click of a button, following your example at the very top. May be you can tell me what I am not doing right or that is not possible to do. Below is my codes. I notice one thing, if I have the APEX_APPLICATION.STOP_APEX_ENGINE in, it stops after download the first file. If I took it out, it only download the last file. Initially I tried without the temp table and call the download after each call of the file creation package, result is the same. I tried in both Chrome and IE.
    Thank you in advance.
    William
    --------------
    DECLARE
    L_ERROR_MESSAGE VARCHAR2(500);
    L_BLOB BLOB;
    L_FILE VARCHAR2(200);
    L_ACCOUNT VARCHAR2(20);
    L_RPT_DATE DATE;
    create_error exception;
    --
    CURSOR C_ALL_STATEMENTS IS
    SELECT ACCOUNT,RPT_DATE
    FROM LIC_ACCOUNT_INVOICE
    WHERE RPT_DATE = :APP_DATE
    AND INVOICE_TYPE = 'S'
    ;
    L_user_id varchar2(30) := :APP_USER;
    L_id varchar2(20) := to_char(sysdate,'YYYYMMDDHH24MISS');
    cursor C_BLOB is
    select file_name,file_data
    from stkrst.lic_settlement_temp
    where apex_user_id = L_user_id
    and id = L_id;
    BEGIN
    FOR rec IN C_ALL_STATEMENTS LOOP
    L_ACCOUNT := rec.ACCOUNT;
    L_RPT_DATE := rec.RPT_DATE;
    L_FILE := NULL;
    IF LIC_SQL.PRINT_SETTLEMENT(L_ERROR_MESSAGE,
    L_FILE,
    L_BLOB,
    L_ACCOUNT,
    L_RPT_DATE,
    'N') THEN
    insert into stkrst.lic_settlement_temp (apex_user_id,id,file_name,file_data)
    values (L_user_id,L_id,L_file,L_blob);
    --
    ELSE
    RAISE CREATE_ERROR;
    END IF;
    END LOOP;
    FOR rec in C_BLOB loop
    L_file := rec.file_name;
    L_blob := rec.file_data;
    htp.init;
    owa_util.mime_header('application/pdf',false);
    htp.p('Content-length: ' || sys.dbms_lob.getlength( L_BLOB ));
    htp.p('Content-Disposition: attachment; filename="' || L_FILE || '"' );
    --htp.p('Cache-Control: max-age=3600'); -- tell the browser to cache for one hour, adjust as necessary
    owa_util.http_header_close;
    wpg_docload.download_file(L_BLOB);
    APEX_APPLICATION.STOP_APEX_ENGINE;
    END LOOP;
    delete from stkrst.lic_settlement_temp where apex_user_id = L_user_id and id = L_id;
    exception
    when create_error then
    apex_error.add_error(
    p_message => L_error_message
    ,p_display_location => apex_error.c_inline_with_field_and_notif
    ,p_region_id => 0
    ,p_column_alias => ''
    ,p_row_num => 0);
    when others then
    apex_error.add_error(
    p_message => sqlerrm
    ,p_display_location => apex_error.c_inline_with_field_and_notif
    ,p_region_id => 0
    ,p_column_alias => ''
    ,p_row_num => 0);
    END;
    --------------------

    ReplyDelete
  28. Hi William,

    I don't know if it's possible to initiate multiple concurrent file downloads all at the same time. You should consider using APEX_ZIP to create a zip archive of your multiple files and downloading that instead.

    The folks from Explorer UK have a nice example about this: http://www.explorer-development.uk.com/apex-5-zip-file-support/

    I hope this helps!

    Joel

    ReplyDelete
  29. Hey Joel,
    I have a application where More as 50 images are display in one of my Page. All Images are blob. The problem is that the Images are not caching. They reload each Time the Page reload. That is not efficient for the Users. You are speak above about caching Images in Browsers. How can I make it? it could very User friendly to have the Images Caching in Client Side.

    Thanks for Answers
    Pierre

    ReplyDelete
  30. Hi Pierre,

    Is this for an unauthenticated application? Or one requiring authentication? If public, you could probably avoid specifying the session ID in the URL, or specify session ID 0, so that the end users have a consistent URL to the images.

    Joel

    ReplyDelete
  31. Hi Joel,
    Very helpful. Thank you very much.

    Olivia

    ReplyDelete
  32. Hi Joel,

    Following SELECT is basis for an APEX interactive report with link column to a form page for maintenance:

    select c.ca_rid, c.carrier_service, c.carrier_code, c.carrier_service_name,
    "IMG_ID" ,
    "FILE_NAME" ,
    "CREATED_DATE" ,
    "CREATED_BY" ,
    "UPDATED_DATE" ,
    "UPDATED_BY" ,
    "MIMETYPE" ,
    "IMG_BLOB" ,
    dbms_lob.getlength(IMG_BLOB) IMG_BLOB_LEN ,

    decode(nvl(dbms_lob.getlength(IMG_BLOB),0),0,null,
    'img style="border: 4px solid #CCC; -moz-border-radius: 4px; -webkit-border-radius: 4px;" '||
    'src="'||
    apex_util.get_blob_file_src('P4_IMG_BLOB', ci."IMG_ID")
    ||
    '" height="75" width="75" alt="Carrier Service Image" title="Carrier Service Image"')
    IMG_BLOB_DTL,

    IMG_BLOB_MD5,
    "IMG_COMMENTS"
    from prod_841_w1.ca_f@prod_841_w1_qry c left outer join lblsym_ca_f_images ci on ci.carrier_service = c.carrier_service

    Opening < and closing /> for img tab excluded from IMG_BLOB_DTL expression above.

    Primary purpose of this simple report/form app is to maintain image mappings. Hence, reason for column IMG_BLOB_DTL above invoking APEX_UTIL.GET_BLOB_FILE_SRC for purpose of presenting image as a report column.

    It works great, displaying images as intended but after a few transitions back and forth between report and form "ORA-01000: maximum open cursors exceeded" is raised. Following statement identifies APEX_PUBLIC_USER,

    select sid, user_name, sql_text, count (*) from v$open_cursor where user_name = 'APEX_PUBLIC_USER' group by sid, user_name, sql_text order by 4 desc

    and this SQL_TEXT,

    begin wpg_docload.get_download_blob(p_blob=>/*out:content*/:

    as likely cause for "ORA-01000: maximum open cursors exceeded".

    Instance parameter OPEN_CURSORS is 300. APEX version is 5.0. RDBMS version is 12.1.0.2.

    I'm thinking APEX_UTIL.GET_BLOB_FILE_SRC call in SELECT statement above is dependent on WPG_DOCLOAD and that all cursors associated with row-level operations implied here should be closed concluding each APEX_UTIL.GET_BLOB_FILE_SRC invocation but that's apparently not what's happening.

    Any idea why number of open cursors continues to increase among APEX_PUBLIC_USER sessions as report page re-renders with each return from maintenance form eventually causing ORA-01000?

    Thanks,

    Glenn

    ReplyDelete