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.

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 - 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 - 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 - Aggregate Validations

In Oracle APEX, validating Interactive Grid (IG) data at row level, is easy and straight forward. You just need to select "Editable Region" for the validation and then you can use IG column values using :COLUMN_NAME syntax in SQL and PL/SQL code. However, if you want to create a validation at table level or validation which uses multiple rows data, then it becomes tricky. Let's consider a simple example of budget planning, where you can allocate percentage for each category, like 10% for healthcare, 10% for education etc. Here, we need to implement a simple table level validation to ensure total allocation % does not exceed 100. Please refer  Interactive Grid - Client Side Aggregate Validations  for implementing this validation entirely on client side. You can use technique explained below for more complex validations that can't be done on client side (checking data from multiple tables, number of rows in IG are more than 50) Before jumping into the implemen