Generating ZIP files in Oracle APEX

APEX_ZIP API was introduced in APEX version 5.0. It has made generating zip files from the database easy. Nothing has changed with this API since then. Here is the sample code to generate a zip file using these APIs.

DECLARE
    l_zip_file   BLOB;
BEGIN
    -- zip all the files uploaded to current application "Static Application Files"
    FOR l_file IN(
        SELECT
            filename       file_name
            ,blob_content   file_content
        FROM
            apex_application_files
        WHERE
            flow_id = :APP_ID
    )LOOP
        apex_zip.add_file(
            p_zipped_blob   => l_zip_file
            ,p_file_name     => l_file.file_name
            ,p_content       => l_file.file_content
        );
    END LOOP;

    BEGIN
        apex_zip.finish(p_zipped_blob   => l_zip_file);
    EXCEPTION
        WHEN value_error THEN
    -- if there is no file added so far, then apex_zip.finish raises VALUE_ERROR
    -- do nothing
            NULL;
    END;
END;

You can find similar code in Oracle APEX documentation as-well.

Creating folder or directory inside the zip file

Creating folders inside zip file is easy and strait forward. All you need to do is, prepend 'folder-name/' to file name (to p_file_name parameter). APEX will automatically create a folder for you and it will keep those files under respective folder.

For e.g. let's modify above code to include all images in one folder called "images" inside the zip file.

DECLARE
    l_zip_file   BLOB;
BEGIN
    -- zip all the files uploaded to current application "Static Application Files"
    FOR l_file IN(
        SELECT
            filename       file_name
            ,blob_content   file_content
            ,mime_type
        FROM
            apex_application_files
        WHERE
            flow_id = :APP_ID
    )LOOP
    -- move all images to images folder
    -- all other files will be directly under the zip folder
        apex_zip.add_file(
            p_zipped_blob   => l_zip_file
            ,p_file_name     => case 
                                    when mime_type like 'image%' then 'images/'||l_file.file_name
                                else 
                                    l_file.file_name
                                end
            ,p_content       => l_file.file_content
        );
    END LOOP;

    BEGIN
        apex_zip.finish(p_zipped_blob   => l_zip_file);
    EXCEPTION
        WHEN value_error THEN
    -- if there is no file added so far, then apex_zip.finish raises VALUE_ERROR
    -- do nothing
            NULL;
    END;
END;

Using APEX_UTIL_WRAP

If you are using this functionality in multiple places in your application, then you may have to repeat the code, which looks more or less same, in several places. Then I thought, why can't I create a wrapper functions on top of APEX_ZIP APIs to make developers task further easy. So, I have added new function get_zip to my open source package apex_util_wrap. To generate a zip file, you just need to pass SQL query and you will get a zip file. Sample code below.

DECLARE
    l_sql   VARCHAR2(4000);
    l_zip   BLOB;
BEGIN
  -- important: order of columns should be same as below, 1st column should be file_name and 2nd column should be file_content (BLOB)
    l_sql := q'[select filename file_name, blob_content file_content from apex_application_files where flow_id = v('APP_ID')]';
    l_zip := apex_util_wrap.get_zip(l_sql);
END;

This works well when query does not have any bind variables or when using this function with-in a valid APEX session (i.e. bind variable values can be passed using V function). But, if you want to use it in any scheduler jobs, then you may need to pass bind variable values separately. In most cases, having one bind variable would be sufficient. So, I have added another overloaded function get_zip. This function accepts SQL query and one bind variable value. Sample code below.

DECLARE
    l_sql   VARCHAR2(4000);
    l_zip   BLOB;
BEGIN
  -- important: order of columns should be same as below, 1st column should be file_name and 2nd column should be file_content (BLOB)
    l_sql := q'[select filename file_name, blob_content file_content from apex_application_files where flow_id = :APP_ID]';
    l_zip := apex_util_wrap.get_zip(
        l_sql
        ,:APP_ID
    );
END; 

Well, what if you need to pass multiple bind variables to the query? Do we need to use vanilla apex_zip APIs again? May be not. I have added yet another overloaded function get_zip. This function accepts SQL query , array of bind variable names and array of bind variable values. Sample code below.

DECLARE
    l_sql   VARCHAR2(4000);
    l_zip   BLOB;
BEGIN

    l_sql := q'[select filename file_name, blob_content file_content from apex_application_files where flow_id = v('APP_ID') and filename in (:FILE1,:FILE2,:FILE3)]';
    l_zip := apex_util_wrap.get_zip(
        l_sql
        ,apex_t_varchar2(
            'FILE1'
            ,'FILE2'
            ,'FILE3'
        )
        ,apex_t_varchar2(
            'captain.jpg'
            ,'ironman.jpg'
            ,'thor.jpg'
        )
    );
END;

Another frequent use case is, generating zip file and downloading it from APEX. You can do this using apex_zip APIs and sys.wpg_docload.download_file procedure, after printing required HTTP headers etc. To make it simple, I have created another wrapper procedure download_blob in apex_util_wrap package. You can use this procedure to download any BLOB file in APEX. 

Sample code to generate and download zip file. You can use put this code in "before header" page process in any APEX page.

DECLARE
    l_sql   VARCHAR2(4000);
    l_zip   BLOB;
BEGIN

    l_sql := q'[select filename file_name, blob_content file_content from apex_application_files where flow_id = v('APP_ID') and filename in (:FILE1,:FILE2,:FILE3)]';
    l_zip := apex_util_wrap.get_zip(
        l_sql
        ,apex_t_varchar2(
            'FILE1'
            ,'FILE2'
            ,'FILE3'
        )
        ,apex_t_varchar2(
            'captain.jpg'
            ,'ironman.jpg'
            ,'thor.jpg'
        )
    );

    apex_util_wrap.download_blob(
        l_zip
        ,'images.zip'
    );
  -- stop APEX Engine
    apex_application.stop_apex_engine;
END;

If you like get_zip function or download_blob procedure, then you can download apex_util_wrap package from here.

Link for demo.