APEX: Download BLOB from temporary table

Question

I'm trying to build a simple viewing application using Oracle APEX 4.1.1. The info to be displayed is in a table on a different database then the database containing the schema the APEX application accesses. This remote table is accessed using a View (RemoteTableView) and a Database Link. The View works as expected, including Oracle's disability to Select a LOB column item through a Database Link.

In the APEX application I have defined a Procedure (DownloadFiles) that is run whenever a BLOB from the View is required to be downloaded, according to the instructions in the Oracle Application Express Advanced Tutorials

This works perfect when the APEX application is build on an existing table containing BLOB items, no problem there.

However, on the RemoteTableView the process differs slightly. Additional lines of code are added to the DownloadFiles Procedure that, whenever an item in the view is called to be downloaded, Insert the actual BLOB from RemoteTableView into a Temporary Table (TempTable). DownloadFile is then called on TempTable to download the (now locally stored) BLOB. (This is all done to circumvent the direct Selection on LOB items through a DB-Link). There is no COMMIT.

Unfortunately, the APEX applications fails whenever the item is called to download with a "This webpage is not found. No webpage was found for the web address: .../f?p=101:7:1342995827199601::NO::P7_DOC_ID:3001".

Research into this problem has proven fruitless. The Insert Procedure is working as expected (in PL/SQL Developer) and any other BLOB in any other local table can be downloaded easily.

Thus the question is, why can't the APEX application handle this situation. Are there limitations when working with temporary tables or insert statements that I should be aware of? Also, what are the best practices for downloading a LOB object.

To elaborate on the procedure to Insert the rows and Download the BLOB. (I've tried different approaches). This PL/SQL block is called 'on load before header', :P2_BLOB_ID is filled with the identifier column value to the BLOB column.

DECLARE
  v_mime      VARCHAR2(48);
  v_length    NUMBER(38);
  v_file_name VARCHAR2(38);
  Lob_loc     BLOB;
BEGIN
  DELETE FROM [TemporaryTable];
  --
  INSERT INTO [TemporaryTable]( [attr1]
                              , [attr2]
                              , [blob]
                              , [mime] )
  SELECT [attr1]
  ,      [attr2]
  ,      [blob]
  ,      [mime]
  FROM   [RemoteTableView]
  WHERE  [attr1] = :P2_BLOB_ID
  AND    ROWNUM  = 1;
  --
  SELECT [mime]
  ,      [blob]
  ,      [attr1]
  ,      DBMS_LOB.GETLENGTH( [blob] )
  INTO   v_mime
  ,      lob_loc
  ,      v_file_name
  ,      v_length
  FROM   [TemporaryTable]
  WHERE  [attr1] = :P2_BLOB_ID;
  --
  owa_util.mime_header( nvl(v_mime,'application/octet'), FALSE );
  htp.p('Content-length: ' || v_length);
  htp.p('Content-Disposition:  attachment; filename="'||replace(replace(substr(v_file_name,instr(v_file_name,'/')+1),chr(10),null),chr(13),null)|| '"');
  owa_util.http_header_close;
  wpg_docload.download_file( Lob_loc );
END;

Answer 1

Try to add apex_application.stop_apex_engine after the wpg_docload call. This will avoid further output of HTTP headers, potentially screwing up your download because further apex code is generated.

  owa_util.mime_header( nvl(v_mime,'application/octet'), FALSE );
  htp.p('Content-length: ' || v_length);
  htp.p('Content-Disposition:  attachment; filename="'||replace(replace(substr(v_file_name,instr(v_file_name,'/')+1),chr(10),null),chr(13),null)|| '"');
  owa_util.http_header_close;
  wpg_docload.download_file( Lob_loc );
  apex_application.stop_apex_engine;

---

Furthermore, to elaborate on :

Are there limitations when working with temporary tables or insert statements that I should be aware of?

Yes. But not necessarily in your case. It is important to remember how apex works with regard to database sessions. Apex is state-less and works with connection pooling. An apex session does not generally match up with 1 database session, and you're never guaranteed that, for example, the same database session is used between render and processing. This is also briefly mentioned in the documentation on Understanding Session State Management, copied for convenience:

HTTP, the protocol over which HTML pages are most often delivered, is a stateless protocol. A web browser is only connected to the server for as long as it takes to download a complete page. In addition, each page request is treated by the server as an independent event, unrelated to any page requests that happened previously or that may occur in the future. To access form values entered on one page on a subsequent page, the values must be stored as session state. Oracle Application Express transparently maintains session state and provides developers with the ability to get and set session state values from any page in the application.

2.4.1 What Is a Session?

A session is a logical construct that establishes persistence (or stateful behavior) across page views. Each session is assigned a unique identifier. The Application Express engine uses this identifier (or session ID) to store and retrieve an application's working set of data (or session state) before and after each page view.

Because sessions are entirely independent of one another, any number of sessions can exist in the database at the same time. A user can also run multiple instances of an application simultaneously in different browsers.

Sessions are logically and physically distinct from Oracle database sessions used to service page requests. A user runs an application in a single Oracle Application Express session from log in to log out with a typical duration measured in minutes or hours. Each page requested during that session results in the Application Express engine creating or reusing an Oracle database session to access database resources. Often these database sessions last just a fraction of a second.

In the case of a global temporary table this means that it is pointless to use in many cases since the data will only exist in that current database session. An example of this is where one would load data in a GTT somewhere in the onload and means to use it in the after-submit processes or an ajax call. Big chance the table will be empty.
Apex however provides an alternative in the form of apex_collection, which will temporarily hold data within a given apex session.