APEX - Downloading files

DATA ARCHITECTURE

by Ludek Bob Jankovsky, 11-Jan-2016 (ORACLE STUFFS)

Using APplication EXpress as a simple GUI cover for Matadata based generators of XML and SQL files led me to task to download these files.
Browsing over various helps I decided to add another one to the rubbish.
There are two basic scenarios:
1. We want to download result of PL/SQL method (function) directly to client machine, or
2. We want to download generated codes from stage - some storage of files prepared for download.

1. Direct download of generated code

Create new page dedicated to download file.
Create items - parameters necessary for the generating process and filename.
Create following process. The process can be situated After Regions. That arrangement allows to do computations on the page before.
The download should be initialized by redirecting to this pages and setup of necessary page items. After the file is downloaded, the process will return back to the calling page.

Declare
    V_CLOB clob:=null;
    V_BLOB blob;
    v_lang_context  integer := DBMS_LOB.DEFAULT_LANG_CTX;
    v_warning       integer := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;
    v_dest_offset   integer := 1;
    v_source_offset integer := 1;
begin
---------------------------------------------------------------------------------------------------
------------------------------logic itself--Generate your file content here in v_CLOB -------------
---------------------------------------------------------------------------------------------------
--something, containing v_CLOB:=
---------------------------------------------------------------------------------------------------
    dbms_lob.createtemporary(v_blob, true, DBMS_LOB.CALL);
    sys.htp.init;
    sys.owa_util.mime_header( 'application/octet-stream', FALSE,'UTF-8' );
    sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( v_clob ));
    sys.htp.p('Content-Disposition: attachment; filename="'||:P18_FILENAME||'"' );
    sys.owa_util.http_header_close;
    dbms_lob.converttoblob (
        dest_lob    => V_BLOB,
        src_clob    => V_CLOB,
        amount      => DBMS_LOB.LOBMAXSIZE,
        dest_offset => v_dest_offset,
        src_offset  => v_source_offset,
        blob_csid   => DBMS_LOB.DEFAULT_CSID,
        lang_context=> v_lang_context,
        warning     => v_warning
    );
   sys.wpg_docload.download_file(v_blob);
   DBMS_LOB.FREETEMPORARY (v_BLOB); --do not forget!!
   apex_application.stop_apex_engine;
exception when others then
    sys.htp.prn('error: '||sqlerrm);
   DBMS_LOB.FREETEMPORARY (v_BLOB); --do not forget!!
   apex_application.stop_apex_engine;
end;

[Download]

2. Prepare files for download
Differently from the first case we can download files stored as BLOB fields in database table.
The way of asynchronous preparing of data for download in database table is more efficient especially when the time of generating is significant. You can let the system to generate your code on background and not to waste your time waiting for your files.
The mentioned way also allows to prepare several files to be downloaded at once.

Create table to store data files:

Create table MAP_USER_STORAGE
(
  MUST_KEY                INTEGER GENERATED AS IDENTITY
,MUST_USER_NAME          VARCHAR2(100 CHAR)
,MUST_DATE               DATE
,MUST_DESC               VARCHAR2(1000 CHAR)
,MUST_DATA               BLOB
,MUST_MIME_TYPE          VARCHAR2 (255 CHAR)
,MUST_FILENAME           VARCHAR2 (255 CHAR)
,MUST_CHARACTER_SET      VARCHAR2 (128 CHAR)
,CONSTRAINT MUST_PK PRIMARY KEY (MUST_KEY)
);

[Download]

Then create a Classic report based on columns of the table above.

The field with the blob data should be defined as shown bellow. All these relevant fields are again defined at the Downloaded BLOB field.

Important!Change the BLOB column in the report SQL query .. don't return BLOB in the report, return numeric constant or key column again instead.

Make sure the key of the table is numeric. I met some difficulties in my version of APEX when I used another type of key.

And finally, there will a "download" link appear in the report as you can see bellow.

Ludek Bob Jankovsky