How to Zip and download files in Oracle APEX?

Introduction

In this tutorial, we will be focusing on how to use the apex_zip utility package to zip files and enable them to be downloaded with just a single button click from within an Oracle APEX page.

Background

In the employee self-service portal, employees frequently request reimbursements for various expenses such as travel allowances, shift allowances, phone reimbursements, and more. To process these requests, employees are required to upload digital copies of their supporting documents to the portal. At the end of each month, the HR admin is responsible for downloading these requests and submitting them to the accounts team. However, this process can be quite time-consuming, especially when it comes to downloading multiple attachments. To simplify this process, we've created a solution that allows users to download all attachments for all requests submitted by employees during a specific period as a single zip file. To learn more about how this solution works, please watch the video provided below.

Steps

To create a functionality to download attachments as a zip file, follow these steps:

1. Create an application process named DOWNLOAD_ATTACHMENTS in the Shared Components or at the page level in the Ajax Callback section.

   

    DECLARE
       l_download_file_name  VARCHAR2(64) := 'reimbursement-attachments.zip';
       l_zip_file        BLOB;
       l_disposition     VARCHAR2(30) := 'attachment';
    BEGIN
       FOR i IN (
          SELECT file_name,
                 file_content
          FROM my_files
       ) LOOP
         -- Add files to the zip 
        apex_zip.add_file(
                           p_zipped_blob => l_zip_file
                         , p_file_name => i.file_name
                         , p_content => i.file_content
          );
       END LOOP;
   
       -- Finish zipping
       apex_zip.finish(p_zipped_blob => l_zip_file);
       -- Download zip file
       sys.htp.init;
       sys.owa_util.mime_header(
                               'application/zip'
                             , false
       );
       sys.htp.p('Content-length: '
                 || sys.dbms_lob.getlength(l_zip_file));
   
       sys.htp.p('Content-Disposition: attachment; filename="'
                 || l_download_file_name
                 || '"');
       sys.owa_util.http_header_close;
       sys.wpg_docload.download_file(l_zip_file);
       apex_application.stop_apex_engine;
    END;
   

2. Create a button and set the behavior to "Submit Page".

   

3. Under the processing tab, create a branch after the submit (or after processing) and set the behavior to:

   • Type: Page or URL (Redirect)

   • Target: Same page

   • Advanced → Request: APPLICATION_PROCESS=DOWNLOAD_ATTACHMENTS

     

4. Save and run the application.

References

https://docs.oracle.com/en/database/oracle/apex/22.2/aeapi/APEX_ZIP.html#GUID-270BFF3A-5FB1-4089-894E-978608F9BD87