Sunday, February 6, 2011

PDF Downloading and Viewing in Oracle Apex 4.0 and 11g

I just had a challenging experience with trying to simply create an Apex page in Apex 4.0 backed by 11gR2 - that displayed rows from a custom table containing files (blobs) in which each row would provide a download link and a second link to preview the file in a separate page or separate region.  I can't find any oracle apex documentation to support this effort and the API's were not entirely working for Apex 4.0.
My other requirement was that I wanted a clean solution - not one filled with lot of code and javascript and calls to other dependencies.  I wanted something simple that would be easily reused elsewhere.

The following is my strange but true story that should help you understand and get started as you build toward a production solution.

Here's what you'll need to build with your Apex Application to support the functionality:

  • Shared Component: Application Item ( for download link )
  • Shared Component: Application Process ( for download link )
  • Custom Blob table with Trigger and Sequence ( to store the blob )
  • Stored Procedure ( for download link )
Getting Started:  Total Confusion
I read a lot of posts on the Apex forums and all it did was confuse the heck out of me.  With that in mind, its important to look at the API's to understand what is going on.
We'll take on functionality in two phases.  Phase 1 is the display functionality - to display a PDF in the browser.

This works in Firefox and IE - hasn't worked in Chrome for me yet.

I give the below information credit to Varad Acharya at this link: http://forums.oracle.com/forums/thread.jspa?threadID=977177&start=0&tstart=0 and it was a key to helping me solve this puzzle.

High Level spoiler:  From the 30k ft view - this is how it works.
My apex application consists of two pages.  The first page has an interactive report region with a query against my custom blob table.  The result set of the query gives me the ability to click a link to download the file or another link to open the PDF (provided it is stored as a PDF) in page 2 within a PLSQL region.

The trick is that i'm not using the WWV_FLOW_FILES view - well, initially I am but I use an after submit process on page 1 to copy it to my custom table.  On Page 2 I have my PLSQL region to display the PDF, but I also have a TEXTAREA page item that has the source of DATABASE_COLUMN, with the column name in my custom table that holds the actual blob.

The strange part is that everywhere you read, they say you need a ARP (Automated Row Process of type DML) in order to tell Apex what table your blob column is - since the TEXTAREA page item only lists a COLUMN and not a specific TABLE.COLUMN.  You will also read that you will need to use a FILE BROWSE item to make this work.  Even the API tells you that (in the spec header).  This didn't work for me, I needed the TEXTAREA page item to make it all work.

What I also found, is that in Apex 4.0, the ARP DML process ( I had on page 2 with condition of NEVER ) didn't work - at least - didn't appear to work.  Everywhere including the Oracle API stated that I needed an ARP of type DML. So what ended up working for me in Apex 4.0 was to use an ARF ( Automated Row Fetch ) on page two AFTER SUBMIT with condition of never.  So this is how Apex 4.0 ties your custom blob table back to the table column defined in your TEXTAREA page item.  In the ARF you simply list the owner, table and PK column and page item that hold the value.  Set the condition to "Never" and you are all set.  As i've read elsewhere, its a "hack" to tell Apex what table to get the column from you list in your textarea item.

Then in the PL/SQL Region (PL/SQL Anonymous block) on page 2, I could list this code as Region Source:
htp.prn('
<embed height="600" width="800" name="embed_content" src="'|| apex_util.get_blob_file_src('P2_BLOB_CONTENT', :P1_ID)||
'" type="application/pdf" />
');

So in the above code, P2_BLOB_CONTENT is my TEXTAREA item with Source Used of Always, and Source Type of DATABASE COLUMN and my custom table column name ONLY in the Source value or expresssion area. No colons, no MIME_TYPE, no ::inline - nothing.  Just the column name thats it.  In my case the column name is BLOB_CONTENT.
The P1_ID in the code is a page item I have on Page 1 that holds the ID of the Primary Key of my Custom table.  So when the user clicks the link in my report ( actually its the ID of the table row, with a branch to page 2, setting P1_ID with #ID# ), Apex sets P1_ID with the ID of the row and goes to Page 2 and looks for the TEXTAREA Item (P2_BLOB_CONTENT) that lists the database column to fetch from - and in turn - looks automagically at the ARF after submit process on page 2 to get the table name.  Then the PDF is displayed in my PL/SQL region.

You might be off and running with the information above, however here are the specifics to my example:

I have a custom blob table defined as:

CREATE TABLE APEXDEV.DOCUMENT_STORE
(
  ID            INTEGER                         NOT NULL,
  NAME          VARCHAR2(400 BYTE)              NOT NULL,
  FILENAME      VARCHAR2(400 BYTE)              NOT NULL,
  MIME_TYPE     VARCHAR2(255 BYTE),
  DOC_SIZE      INTEGER,
  DAD_CHARSET   VARCHAR2(128 BYTE),
  CONTENT_TYPE  VARCHAR2(128 BYTE),
  FILE_TYPE     VARCHAR2(255 BYTE),
  FILE_CHARSET  VARCHAR2(128 BYTE),
  DESCRIPTION   VARCHAR2(500 BYTE),
  CREATED_BY    VARCHAR2(64 BYTE),
  CREATED_DT    DATE,
  UPDATED_BY    VARCHAR2(64 BYTE),
  UPDATED_DT    DATE,
  BLOB_CONTENT  BLOB
);

My BLOB_CONTENT is actually stored out of line in another tablespace - but thats beyond the scope here.

CREATE SEQUENCE APEXDEV.SEQ_DOCUMENT_STORE_ID
  START WITH 1
  INCREMENT BY 1
  NOCACHE
  NOCYCLE;


CREATE INDEX APEXDEV.IDX_DOCUMENT_STORE ON APEXDEV.DOCUMENT_STORE
(ID) ;

ALTER TABLE APEXDEV.DOCUMENT_STORE ADD CONSTRAINT PK_DOCUMENT_STORE_ID
PRIMARY KEY (ID) USING INDEX;

CREATE OR REPLACE TRIGGER APEXDEV.BI_DOCUMENT_STORE_TRG
before insert or update ON APEXDEV.DOCUMENT_STORE for each row
begin
 IF INSERTING THEN
  select SEQ_DOCUMENT_STORE_ID.NEXTVAL into :NEW.ID from dual ;
  :NEW.created_by := nvl(wwv_flow.g_user,user);
  :NEW.created_dt := sysdate ;
  :NEW.updated_by := nvl(wwv_flow.g_user,user);
  :NEW.updated_dt := sysdate ;
  
 ELSIF UPDATING THEN
  :NEW.updated_by := nvl(wwv_flow.g_user,user);
  :NEW.updated_dt := sysdate ;
  
  END IF;
end;
/

So what I do is create a file browse item (P1_BROWSE) that inserts into the typical WWV_FLOW_FILES view.  I then have an on submit process to insert the blob into my custom table and remove the row from WWV_FLOW_FILES. 

begin

if ( :P1_BROWSE is NOT NULL ) then
insert into document_store (
                  name
                 ,filename
                 ,mime_type
                 ,doc_size
                 ,dad_charset
                 ,content_type
                 ,file_type
                 ,file_charset
                 ,description
                 ,blob_content )
          select name
                 ,filename
                 ,mime_type
                 ,doc_size
                 ,dad_charset
                 ,content_type
                 ,file_type
                 ,file_charset
                 ,description
                 ,blob_content
           from apex_application_files
           where name = :P1_BROWSE ;

           delete from apex_application_files
            where name = :P1_BROWSE;
end if;
commit;
end;

The key API to display the PDF is a synonym for a package function called APEX_UTIL.GET_BLOB_FILE_SRC

function get_blob_file_src (
    -- Generates a call to the apex_util.get_blob_file that can be used to download a BLOB column content
    -- Only generates usable output if called from a valid APEX session
    -- Example:
    --    PLSQL Function Body: return '<img src="'||apex_util.get_blob_file_src('P2_ATTACHMENT',empno)||'" />';
    --
    p_item_name in varchar2 default null,           -- Name of valid application page ITEM that with type FILE, and source type of DB column
    p_v1        in varchar2 default null,           -- Value of primary key column
    p_v2        in varchar2 default null,           -- Optional value of second primary key column
    p_content_disposition in varchar2 default null) -- Optional content disposition, valid values are "inline" and "attachment", other values ignored
    return varchar2
    ;


So in plain English, use this API to help show a blob or download a blob ( as in 'Save As' popup dialog from your browser ).

The two input parameters work essentially the same as they did pre-Apex 4.0, but somethings have changed - or maybe have become "more flexible".

What this API basically does is call a second synonym API called APEX_UTIL.GET_BLOB_FILE.
This second API is one you do not directly call.  It is called by GET_BLOB_FILE_SRC.  But looking at it does lend a lot of insight into how this works in Apex.

procedure get_blob_file(
    -- Automatically called from APEX form pages
    -- Not designed to be called proceduarlly
    -- Calls to this procedure can be generated by calling the apex_util.get_blob_file_src function
    -- Page must have item of type FILE (FILE Browse)
    -- Page item source must use the following format "DB_COLUMN:MIMETYPE_COLUMN:FILENAME_COLUMN:LAST_UPDATE_COLUMN:CHARSET_COLUMN:CONTENT_DISPOSITION:DOWNLOAD_LINK"
    --    DB_COLUMN           = Required case sensitive name of a valid column which is of type BLOB
    --    MIMETYPE_COLUMN     = Optional case sensitive column name of a table column used to store the mimetype
    --    FILENAME_COLUMN     = Optional case sensitive column name of a table column used to store the file name
    --    LAST_UPDATE_COLUMN  = Optional case sensitive column name of a table column used to store the last update date of the BLOB
    --    CHARSET_COLUMN      = Optional case sensitive column name of a table column used to store the file character set
    --    CONTENT_DISPOSITION = inline or attachment
    --    DOWNLOAD_LINK       = Optional text to be used for the download text, defaults to Download, translated
    -- Page item source must include at least the database column name and a trailing colon
    -- Mimetype column is required if the mimetype is to be encoded in the download header
    -- Page item must be of source type of DATABASE COLUMN
    -- Page must have a DML process of type DML_PROCESS_ROW, used to determine the tablename
    -- Must be called from an APEX application context
    -- Invalid inputs will result in a 404 error
    --
    s                     in number,                -- APEX session ID
    a                     in number,                -- APEX application ID
    p                     in number,                -- APEX page ID of the form page
    d                     in number,                -- DML process APEX meta data ID
    i                     in number,                -- ITEM of type FILE APEX meta data ID
    p_pk1                 in varchar2,              -- Primary key value
    p_ck                  in varchar2,              -- Checksum used to prevent URL Tampering
    p_pk2                 in varchar2 default null, -- Optional Second Primary Key Value, used for compound keys
    p_mimetype            in varchar2 default null, -- Optional ...
    p_content_disposition in varchar2 default null, -- Optional use "inline" or "attachment" all other values ignored
    p_show_last_mod       in varchar2 default 'Y'); -- Optional ...

For Apex 4.0+ the magic all happens on page 2 of my app with the PL/SQL region code of:

htp.prn('
<embed height="600" width="800" name="embed_content" src="'|| apex_util.get_blob_file_src('P2_BLOB_CONTENT', :P1_ID)||
'" type="application/pdf" />
');

Note the call to APEX_UTIL.GET_BLOB_FILE_SRC.  The first parameter is the name of a Page Item that (works for me) as a Textarea type.  This item is on Page 2 ( where my PDF is displayed ).  In fact, just about all the plumbing to get this to work is on Page 2 - and i'm branching from page 1 to page 2 to display the PDF from my interactive report row that contains the PK ID value of my custom blob table.  The second parameter is the the actual page item that will hold the value of this PK ID.

So for Apex 4.0, you can see the discrepancies in the APEX_UTIL.GET_BLOB_FILE API ( called from APEX_UTIL.GET_BLOB_FILE_SRC ), specifically one by one:

 -- Automatically called from APEX form pages  UH OK THANKS.
    -- Not designed to be called proceduarlly   UH OK is that how to spell procedurally, reminds me of having to talk to cops on Friday nights.     
    -- Calls to this procedure can be generated by calling the apex_util.get_blob_file_src function RIGHT - this is called from GET_BLOB_FILE_SRC
    -- Page must have item of type FILE (FILE Browse) This is not right - need a TEXTAREA object
    -- Page item source must use the following format "DB_COLUMN:MIMETYPE_COLUMN:FILENAME_COLUMN:LAST_UPDATE_COLUMN:CHARSET_COLUMN:CONTENT_DISPOSITION:DOWNLOAD_LINK" Well this is not right either anymore, since all you need to do is list the column name of the Database Column that hold the blob. Nothing else.
   


 --    DB_COLUMN           = Required case sensitive name of a valid column which is of type BLOB
    --    MIMETYPE_COLUMN     = Optional case sensitive column name of a table column used to store the mimetype
    --    FILENAME_COLUMN     = Optional case sensitive column name of a table column used to store the file name
    --    LAST_UPDATE_COLUMN  = Optional case sensitive column name of a table column used to store the last update date of the BLOB
    --    CHARSET_COLUMN      = Optional case sensitive column name of a table column used to store the file character set
    --    CONTENT_DISPOSITION = inline or attachment
    --    DOWNLOAD_LINK       = Optional text to be used for the download text, defaults to Download, translated
All the above is not applicable anymore.

    -- Page item source must include at least the database column name and a trailing colon Nope.  Just the column name.
    -- Mimetype column is required if the mimetype is to be encoded in the download header  Nope.  Just the column name.
    -- Page item must be of source type of DATABASE COLUMN  Wow they got one right. Winner winner chicken dinner.
    -- Page must have a DML process of type DML_PROCESS_ROW, used to determine the tablename Dang it they were on a tear - this is wrong, it needs to be an Automated Row Fetch process - after submit.

    -- Must be called from an APEX application context Ya ya ya, app_id, session_id right right
    -- Invalid inputs will result in a 404 error Or is just won't "work" and rediculously hard to debug and trace.

I don't mean to pick on Oracle - I actually love Oracle stuff, I just wish they would update their documentation, because even in the online docs - there is nothing to tell you about how to do this right.

I hope to have some screen shots soon.  Its superbowl Sunday afterall.

www.adminsdirect.com



5 comments:

  1. Very timely - worked a treat :-)

    ReplyDelete
  2. Hi
    What if I have to links from two different database tables, more than Automatic Row Fetch will not work, only on link is working. Is there any workaround.

    Regards,
    Omar

    ReplyDelete
  3. Thanks for the very detailed information. I was struggling with same issues with documentation as well.

    I have few questions as I notice strange issues in various browsers:

    (1) In Firefox I notice that PDF does not expand to 100% width of pl/sql region. Do you know how to fix this.
    (2) In IE, it expands to full width, but does not show PDF controls (save, print etc).
    (3) Were you able to find out how to make this work in Chrome.

    Thanks,
    Anil

    ReplyDelete
  4. Thanks for sharing this great information I am impressed by the information that you have on this blog. Same as your blog i found another one Oracle APEX . Actually I was looking for the same information on internet for Oracle APEX and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject, you can learn more aboutOracle APEX . By attending Oracle APEX Training.

    ReplyDelete
  5. Thanks for sharing this blog. The content is beneficial and useful. Very informative post. Visit here to learn more about Data Warehousing companies and Data analytics Companies. I am impressed by the information that you have on this blog. Thanks once more for all the details.Visit here for Top Big Data Companies.

    ReplyDelete