Simple File Download in Oracle APEX

Files are permanent companions of any web application. Since early versions of Oracle APEX there was a constant headache – files download. But everything changed and I’ll show you how.

I know three most common approaches to download file from Oracle APEX:

1. Download Dynamic Action
2. Interactive Report
3. PL/SQL approach

Download Dynamic Action

The newest and the easiest way to download file from Oracle APEX. Just select Download Action in section Execute for Dynamic action and provide SQL query that returns BLOB, CLOB or VARCHAR2. Query from dual table also possible, which means that functions returning BLOBs or item-stored CLOB’s can be used. Check the example.

Create the table to store the files.

  CREATE TABLE "D_DOCUMENTS" 
   ( "D_DOCUMENTS_ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY NOT NULL ENABLE, 
     "FILE_NAME" VARCHAR2(1000) NOT NULL ENABLE, 
     "DOCUMENT" BLOB NOT NULL ENABLE, 
     "FILE_MIME_TYPE" VARCHAR2(1000),
     "CREATED_BY" VARCHAR2(1000) DEFAULT NVL(SYS_CONTEXT('APEX$SESSION', 'APP_USER'), USER) NOT NULL ENABLE, 
     "CREATED_ON" TIMESTAMP (6) WITH LOCAL TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL ENABLE, 
     "UPDATED_BY" VARCHAR2(1000), 
     "UPDATED_ON" TIMESTAMP (6) WITH LOCAL TIME ZONE, 
      PRIMARY KEY ("D_DOCUMENTS_ID")
   ) ;

In APEX application, create button DOWNLOAD with Defined by Dynamic Action behaviour.

Create Click event Dynamic Action for button DOWNLOAD.

And for True actions create Download action. Download action present under Execute section and available since APEX 24.1 release.

Also Multiple Files could be downloaded as zip and file could be displayed as current page instead of download (View File as Inline). The same Download process available as Process Type but have small restriction. If the page attribute Reload on Submit is set to Only for Success then the only valid position (execution point) for download is Before Header. When page attribute Reload on Submit is set to Always then download process could be placed anywhere. In case this rule violated there will be an error during page save.

Interactive Report

One more option to download file from Oracle APEX. Interactive Report has built-in column type Download BLOB. In case when report needed, that’s a convenient option, just format column as you wish and job is done.

It works pretty good, but supports only BLOB column type and query from dual is not possible. Which means that function returning BLOB or CLOB cannot be used here. These restrictions limits possible use cases of this approach. Also display file as Inline possible.

PL/SQL approach

For those ones who worked with PL/SQL there is a stored procedure WPG_DOCLOAD.download_file. Until download dynamic action introduces, this one was the only option to download file from APEX and still valid approach to do this.

To use WPG_DOCLOAD I made the PL/SQL function download_file

CREATE OR REPLACE PROCEDURE download_file (in_file_name  IN VARCHAR2) IS
  l_blob          d_documents.document%TYPE;
  l_mime_type     d_documents.file_mime_type%TYPE;
BEGIN
  SELECT document,
         file_mime_type
    INTO l_blob,
         l_mime_type
    FROM d_documents
   WHERE file_name = in_file_name;
sys.HTP.init;
  sys.OWA_UTIL.mime_header(l_mime_type, FALSE);
  sys.HTP.p('Content-Length: ' || DBMS_LOB.getlength(l_blob));
  sys.HTP.p('Content-Disposition: filename="' || in_file_name || '"');
  sys.OWA_UTIL.http_header_close;
sys.WPG_DOCLOAD.download_file(l_blob);
  apex_application.stop_apex_engine;
END download_file;

And I use it Before Header. But it’s also possible to use this at Processing point. To do this, page attribute Reload on Submit should be set to Always. So, the flow is next: User click Download button, page submitted and when page loads, actual file download in browser occurs. Using this procedure at Execute Server-side Code Dynamic Action does not work.

APEX_HTTP

Since last APEX versions, APEX team added package APEX_HTTP that actually do the same but easier. Restrictions for all PL/SQL download procedures left the same, when page attribute Reload on Submit set to Only for Success the only valid download process position is After Header. APEX_HTTP package works with BLOB and CLOB types.

DECLARE
  l_file           blob;
  l_mime_type      varchar2(4000);
  l_filename       varchar2(4000);
BEGIN
  SELECT blob_content,
         mime_type,
         filename
    INTO l_file,
         l_mime_type,
         l_filename
    FROM d_documents
   WHERE file_name = :P14_FILE;
apex_http.download( p_blob           => l_file,
                      p_content_type   => l_mime_type,
                      p_filename       => l_filename );
END;

Oracle APEX is a quick growing platforms that pleases us with new features every release. Oracle did a path from old-school approach to modern and simple one. I really like this new feature and will definitely use it every time.

Thank you for reading!