Download files stored on Oracle server FROM Oracle APEX page

Requirement: Download  files stored on Oracle server from Oracle APEX page

Summary: 
In this blog, I am going to put up steps to download files kept/FTP'ed/Uploaded on Oracle server from Oracle APEX page.

Solution:

• Create Oracle directory for the server directory where files are stored:

E.g. CREATE DIRECTORY MY_FILES AS '/u01/app/oracle/product/custom';

• Create a new table with columns as below:

CREATE TABLE BFILE_DEMO

   ( BFILE_NAME VARCHAR2(200 BYTE), 

BFILE_LOB BFILE

   );

• Now, insert records in above table for the files to download. E.g.

INSERT INTO TABLE bfile_demo

VALUES ('file1.txt', BFILENAME('MY_FILES', 'file1.txt'));

COMMIT;

• Create an APEX page with a before header process code as below:

DECLARE

   l_file_name     VARCHAR2(200);

   l_bfile         BFILE;

   l_bfile_length  NUMBER;

BEGIN

   l_bfile := null;

   SELECT BFILE_NAME, BFILE_LOB 

     INTO l_file_name, l_bfile

     FROM bfile_demo

    WHERE bfile_name = 'file1.txt';

    

   l_bfile_length := DBMS_LOB.getlength (l_bfile);

   -- Generate your file content 

   sys.htp.init;

   sys.owa_util.mime_header('application/octet-stream', FALSE, 'UTF-8' );

   sys.htp.p('Content-length: ' || l_bfile_length);

   sys.htp.p('Content-Disposition: inline; filename="' || l_file_name || '"' );

   sys.owa_util.http_header_close;

   sys.wpg_docload.download_file(l_bfile);

   apex_application.stop_apex_engine;

EXCEPTION 

   WHEN OTHERS 

   THEN

      sys.htp.prn('error: '||sqlerrm);

      apex_application.stop_apex_engine;

END;