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.

Answer 2

I was able to use the APEX_COLLECTIONS and corresponding procedures to achieve my desired result.

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;
  --
  APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION('COLLECTION1');
  --
  apex_collection.add_member( p_collection_name => 'COLLECTION1'
                            , p_c001 => v_mime
                            , p_c002 => v_file_name
                            , p_n001 => v_length
                            , p_blob001 => lob_loc
                            );
  --
  DELETE FROM [TemporaryTable];
  --
  SELECT c001, blob001, c002, n001
  INTO   v_mime,lob_loc,v_file_name,v_length
  FROM   apex_collections
  WHERE  collection_name = 'COLLECTION1'
  AND    c002            = :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;

The BLOB is still called from a Temporary Table, after it is inserted there from the remote table. But then stored from the Temporary table in the APEX_COLLECTIONS, from where it is finally retrieved once it is called to be downloaded.

Answer 3

Remotely Querying CLOB or BLOB Data Across a Database Link Using Oracle PL/SQL

The following solution was developed and tested on an Oracle 11gR2 instance. APEX was used just for visualization of the data values as stored or delivered through different intermediate objects. This solution may still prove helpful for application development on APEX equipped DB instances with remotely shared connections such as Oracle DBLinks.

INTRO: There were a few holes in the explanation of the OP, but I have assumed that the usage of terminology such as "Remote Table" and "Database Link" indicates that the OP is trying to bypass a known limitation of the Oracle RDBMS.

This limitation is the inability to query LOB typed data across remote dblink connections. A common error encountered when attempting this is: ORA-22992: cannot use LOB locators selected from remote tables.

Research and References

• A blog post on: Querying CLOB or BLOB Data Across DBLinks This was my primary reference used to develop my walk through... which really is just an recap of my efforts to verify that the code at this link actually works (it does!).

• A reference from PSOUG.org: Reference on the DBMS_LOB Oracle package. Used to verify my understanding of CLOB and LOB data type handling.

• Special mention to: watchout4snakes. This is an online randomized dictionary tool. I used it here to make large chunks of text that didn't make one's eyes glaze over such as: lorum ipsum locati quorum... :)

If you check out the first reference in this list, you may notice my code here is very similar. The value I've added in re-presenting this case from back in 2010 is creating a larger but more interesting set of test data. I've also added a few tweaks to the original solution to help validate the approach.

One More Note: Before you dive into the example, be sure to map out (at least in your head) the schema/owner of the objects created in each the "remote" and "local" instance of your demo... it will ease the level of grief encountered with this otherwise really straight forward solution.

Setting Up The Demo on the REMOTE SERVER

Following is the source code to set up the REMOTE DATABASE INSTANCE.

Database Object Types For Remote Instance

 CREATE TYPE object_row_type AS OBJECT (
    MY_ORDER   NUMBER,
    MY_ID      NUMBER,
    MY_CLOB_AS_VARCHAR   VARCHAR2(4000));

 CREATE TYPE object_table_type AS TABLE OF object_row_type;

Table DDL and Test Data Scripts

 CREATE TABLE REMOTE_CLOB_TABLE (
    ID       NUMBER      NOT NULL,
    MY_CLOB  CLOB
 );


 INSERT INTO REMOTE_CLOB_TABLE(id, my_clob) 
    VALUES(1001, 'When can the expenditure enter behind a shock recovery? The strategy fishes underneath the sugar. An after wrap masters a slim moron. The twenty dish hunts an aunt opposite the credible zone. The phoenix copes. The diagonal flours a bag against the positive fan.');

 INSERT INTO REMOTE_CLOB_TABLE(id, my_clob) 
    VALUES(2001, 'A traveled concept hides a removed skin. A liquid steers whatever understandable heart. A curve strips away an assembly. A wartime freezes the outcome.');

 INSERT INTO REMOTE_CLOB_TABLE(id, my_clob) 
    VALUES(3001, 'A lonely genre bolts beside the obliging prisoner. The freedom stamps! Its game fluid dictates. How will her power imagine the quantum?');

 INSERT INTO REMOTE_CLOB_TABLE(id, my_clob) 
    VALUES(4001, 'The young office caps the travelled temper. A forum husbands the family. The detail peers. Her jammed agenda experiments against the regarding obstruction.');

 INSERT INTO REMOTE_CLOB_TABLE(id, my_clob) 
    VALUES(5001, 'The incredible drivel suspects. A vehicle reads. A cardboard jacket shares the insult above the baking constitutional. Outside this effort composes the invited jest.');

 INSERT INTO REMOTE_CLOB_TABLE(id, my_clob) 
    VALUES(6001, 'The vegetarian strength marches underneath an opposing intellectual. The ringed lifestyle bends the archaic thirst. A saga escapes with the ego. The acorn escapes against the old lark.');

 INSERT INTO REMOTE_CLOB_TABLE(id, my_clob) 
    VALUES(7001, 'Whatever bear furthers a mania. The norm contours a ruin. How can the reasoned composite cough? With a unimportant timetable reasons the sorry frog. Can the key jacket toss an author?');

 COMMIT;

Function Object Script

 CREATE or REPLACE FUNCTION CONVERT_CLOB_TO_VARCHAR RETURN object_table_type PIPELINED
    IS 

 PRAGMA AUTONOMOUS_TRANSACTION;

    v_clob_length   number;
    v_loops         number;
    v_varchar_size  number := 100; 
    -- purposely set to a small value to see the looping step work
    -- normally set to max varchar2 size (4000 BYTE).

 BEGIN

 FOR cur IN (SELECT id, my_clob from remote_clob_table)  
 LOOP

       v_clob_length := dbms_lob.getlength (cur.my_clob);
       v_loops := trunc(v_clob_length/v_varchar_size) + 
          sign (mod(v_clob_length, v_varchar_size )) - 1;


    FOR i IN 0..v_loops
    LOOP

       -- This chunks the CLOB/LOB file from beginning to end in chunks
       --   of pre-designated size.

       PIPE ROW(object_row_type(i+1, cur.id, dbms_lob.substr(cur.my_clob,
          v_varchar_size, v_varchar_size * i + 1 )));

    END LOOP;


 END LOOP;

 COMMIT;
 RETURN;

 END;

The VIEW Object for Presenting Remotely Accessible Converted CLOB Data:

 CREATE or REPLACE VIEW myremotedata AS
    SELECT * FROM TABLE(CONVERT_CLOB_TO_VARCHAR) a;

Setting Up The Demo on the LOCAL SERVER

Following is the source code to set up the LOCAL DATABASE INSTANCE.

Make the DB Link to the Remote Server:

 CREATE PUBLIC DATABASE LINK MY_REMOTE_CONNECTION
    CONNECT TO REMOTE_USER
    IDENTIFIED BY <PWD>
    USING <TNS or DIRECT CONNECTION STRING>

Declaring Custom Object Type:

 CREATE OR REPLACE TYPE MY_TABLE_TYPE AS TABLE OF VARCHAR2(4000);

PL/SQL Function for Re-Assembling VARCHAR Pieces to CLOB Format

 CREATE OR REPLACE FUNCTION F_VARCHAR_TO_CLOB (input_table_of_varchar  my_table_type)
    RETURN CLOB IS
    PRAGMA AUTONOMOUS_TRANSACTION;

    v_clob   clob;

 BEGIN
    FOR i in 1..input_table_of_varchar.COUNT
    LOOP

    v_clob := v_clob || input_table_of_varchar(i);

    END LOOP;

 RETURN v_clob;

 END;

View Object for Collecting Results From Across the Database Link

 CREATE OR REPLACE VIEW MY_REMOTE_DATA AS

    SELECT a.id,
      f_varchar_to_clob( 
         CAST( MULTISET( SELECT b.MY_CLOB_AS_VARCHAR
                           FROM remote_user.myremotedata@my_remote_connection b
                          WHERE a.id = b.my_id
                          ORDER BY b.my_id ASC, b.my_order ASC ) 
                                     as my_table_type)

                     ) MY_CLOB

 FROM REMOTE_CLOB_TABLE@my_remote_connection a;

Output Results

Data queried remotely from the local instance is presented in two reports. The first shows how a CLOB data type can be digested into smaller pieces. The max size of each piece is actually variable. I chose a smaller size than the 4,000 byte varchar2 maximum so that you can see a slightly smaller CLOB data value divided into many pieces.

Output From a View on the Remote Instance: MYREMOTEDATA

This was created using the CONVERT_CLOB_TO_VARCHAR PL/SQL Function.

Output From a View on the Local Instance: (Reassembled data in CLOB Format)

select id, my_clob from my_remote_data where id between 1000 and 5000

ID MY_CLOB

1001 When can the expenditure enter behind a shock recovery? The strategy fishes unde rneath the sugar. An after wrap masters a slim moron. The twenty dish hunts an a unt opposite the credible zone. The phoenix copes. The diagonal flours a bag aga inst the positive fan.
2001
A traveled concept hides a removed skin. A liquid steers whatever understandable heart. A curve strips away an assembly. A wartime freezes the outcome.
3001
A lonely genre bolts beside the obliging prisoner. The freedom stamps! Its game fluid dictates. How will her power imagine the quantum?
4001
The young office caps the travelled temper. A forum husbands the family. The det ail peers. Her jammed agenda experiments against the regarding obstruction.

4 rows selected.

After querying the VARCHAR data pieces, this is the view that reconnects them all back to CLOB format.

Conclusions:

This is a proof-of-concept approach which illustrates how to bypass the Oracle RDBMS limitation between Custom User Types, Collections, CLOBs, and BLOBs when attempting a remote query over a database link.

Additional analysis may be necessary to evaluate any possible performance bottlenecks or issues related to the size, quantity and transfer rates of the data queried between a more specific local and remote instance (i.e., your own intended environment).