Skip to main content

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.


Comments

Kinjan Bhavsar said…
Hi Srihari,

Can we create a folder(directory) inside zip file using APEX_ZIP or APEX_UTIL_WRAP API?
Hari said…
Hi Kinjan,

You can. All you need to do is, prepend 'folder-name/' to file name. APEX will automatically create a folder for you and it will keep those files under respective folder.

For e.g. if you want to keep all images in one folder and others in other folder, then use SQL query as below.


select CASE WHEN MIME_TYPE LIKE 'image%' THEN 'images/'||FILENAME ELSE 'others/'||FILENAME end file_name, BLOB_CONTENT file_content
from apex_application_files
where FLOW_ID = v('APP_ID')


You can also do it using apex_zip.add_file, just prepend 'folder-name/' to p_file_name value.

e.g.

apex_zip.add_file(
p_zipped_blob => l_zip_file
,p_file_name => 'folder-name/'||l_file.file_name
,p_content => l_file.file_content
);
Kinjan Bhavsar said…
Thanks Srihari. I will give it a try👍
Hi. I have an Oracle Directory that read the content of a directory on the server. I have created an external table to view the content of the oracle directory and I'm able to download a single file.
Now I want to give end user to download the files he has selected and put them in a zip file to download on his machine.

The external table is as follow:

FILE_NAME VARCHAR2(500 BYTE)
FILE_PERMISSIONS VARCHAR2(11 BYTE)
FILE_HARDLINKS NUMBER
FILE_OWNER VARCHAR2(100 BYTE)
FILE_GROUP VARCHAR2(100 BYTE)
FILE_SIZE NUMBER
FILE_DATETIME DATE

How can I add files selected from user into a zip file and then download the zip file on machine?
Thanks in advance,
Fabrizio
Bhumika said…
Hi Srihari,
Can you tell What is the size limit for all files?
I am using "Download Zip" option in my application. It is giving "numeric or value error" as it contains minimum 50 files.
Hari said…
Hi Fabrizio,

You can use dbms_lob.loadblobfromfile to read files from Oracle directory to BLOB and then you can use apex_zip APIs to create a zip file.
Hari said…
Hi Bhumika,

I don't think there is limit on number of files. I have exported APEX application as zip file where application has more than 500 pages. Behind the scenes, APEX uses apex_zip to generate application export zip file. So, there is no such limit on number of files.

However, without seeing your code, it's difficult to guess why you are getting this error. I suggest to post your question to Oracle forums with sample code and other required info.
Bhumika said…
Hi Srihari,

My issue is resolved. It was generating an error due to some null file records exist in the table. Excluding that files, now we can download zip.

Popular posts from this blog

Interactive Grid - Conditional Enable/Disable

In this blogpost, I am going to discuss few approaches using which we can conditionally enable/disable Interactive Grid (IG) column(s) based on other column(s) values. Note:    There is a bug  30801170  in APEX 19.2/20.1 with respect to "enable/disable" dynamic actions for IG columns. Workaround for this bug is provided at the end of this blogpost . This bug has been fixed in APEX version 20.2. Client Side Only Conditions If conditions to enable/disable are simple, then we can check those conditions easily on the client side. For e.g. let's consider IG on EMP table with following SQL Query. SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP Let's consider the requirement as - Enable "Commission" column only when JOB is equals to 'SALESMAN' and disable "Commission" column in all other cases. This can be done declaratively using dynamic actions (DA) DA "Enable/Disable Commission - 1" Create DA and give it a prope

Interactive Grid - Bulk Operation on Selected Rows

What's the Problem? Let's say you have an IG on employee table and you want to update selected employees commission based on user's input. Ideally, it should be very simple, where you can write UPDATE statement in page process and select IG region as "Editable Region" under "Execution Options" of the page process. But, when you select rows and submit page, you can see that, this process won't get executed! The reason is  Selection of 'Row Selector' check-boxes is not considered as row-change. Thus selected rows are not submitted to server. So, is there any work around?  Yes! Luckily there are lot of JavaScript (JS) APIs available to work with IG. If you are not already aware, you can refer "APEX IG Cookbook"  or  JavaScript API Reference documentation. If we continue with above Employee IG example, when user selects IG rows, enters "Commission %" and clicks on "Update Commission" button, then we can writ

Interactive Grid - Process Filtered Data on Server Side

Recently one of the APEX developers has reached out to me and asked if it's possible to capture filtered rows data of the Interactive Grid on the server-side and do some processing. In APEX 20.1, there is a new API APEX_IG , using which we can achieve this. Photo by Jakub Kapusnak on Unsplash The approach is very simple and straightforward. Get the internal region id based on the Static ID given for the IG region Get the last viewed report id based on region id Open query context for the region and report using region id and report id Fetch and loop through the rows using the query context Do something with fetched rows And finally, close the query context If you have already done this for interactive reports, then you should be already aware of these steps. The only difference here is, we use APEX_IG APIs instead of APEX_IR APIs. For the demo purpose, let's Build an Interactive Grid on EMP table and let's give it a Static ID as emp Create a Textarea page item with