A Comprehensive Guide to the APEX_ZIP Package in Oracle APEX

Introduction

When developing enterprise applications with Oracle APEX, there are scenarios where users need to download multiple files in a compressed format (ZIP). For example:

• Users want to download a group of files at once.

• A report generates multiple output files.

• Compressed data needs to be sent via an API.

In such cases, the built-in APEX_ZIP package comes in handy, allowing you to dynamically generate ZIP files using PL/SQL.

---

What Is APEX_ZIP?

APEX_ZIP is an internal API in Oracle APEX designed for creating, managing, and compressing files into ZIP format.

Using this package, you can:

• Create ZIP files that contain multiple text or binary files.

• Add file content from either BLOB or VARCHAR2.

• Deliver the final ZIP to users as a download or store it in the database.

---

Use Cases of APEX_ZIP

1. Providing batch file downloads for users

2. Sending compressed payloads to external services

3. Automatically archiving reports or generated files

4. Exporting CLOB or BLOB content in a single ZIP file

---

Main Procedures and Functions in APEX_ZIP

1. APEX_ZIP.INIT

You must initialize a BLOB variable to start building the ZIP structure.

PROCEDURE INIT (
   p_zipped_blob IN OUT NOCOPY BLOB );

---

2. APEX_ZIP.ADD_FILE

This procedure adds a file to the ZIP. It comes in two overloads:

Add a text file:

APEX_ZIP.ADD_FILE (
   p_zipped_blob IN OUT NOCOPY BLOB,
   p_file_name   IN VARCHAR2,
   p_content     IN VARCHAR2 );

Add a binary file (BLOB):

APEX_ZIP.ADD_FILE (
   p_zipped_blob IN OUT NOCOPY BLOB,
   p_file_name   IN VARCHAR2,
   p_content     IN BLOB );

Parameters:

• p_zipped_blob: The target BLOB variable holding the ZIP structure.

• p_file_name: The name of the file to be added inside the ZIP.

• p_content: The file content, either text (VARCHAR2) or binary (BLOB).

---

3. APEX_ZIP.FINISH

After adding all files, finalize the process and get the complete ZIP as a BLOB.

FUNCTION FINISH (
   p_zipped_blob IN BLOB )
RETURN BLOB;

The result is a complete ZIP archive in BLOB format.

---

Practical Example: Create a ZIP with Two Text Files

This example creates a ZIP file with two text files and sends it to the browser for download.

DECLARE
  l_zip BLOB;
  l_final_zip BLOB;
BEGIN
  -- Initialize ZIP
  APEX_ZIP.INIT(l_zip);

  -- First file
  APEX_ZIP.ADD_FILE(
    p_zipped_blob => l_zip,
    p_file_name   => 'hello.txt',
    p_content     => 'Hello Mahdi! This is your ZIP file.');

  -- Second file
  APEX_ZIP.ADD_FILE(
    p_zipped_blob => l_zip,
    p_file_name   => 'info.txt',
    p_content     => 'This file was generated using APEX_ZIP for testing.');

  -- Finalize ZIP
  l_final_zip := APEX_ZIP.FINISH(l_zip);

  -- Send ZIP to browser
  owa_util.mime_header('application/zip', FALSE);
  htp.p('Content-Disposition: attachment; filename="example.zip"');
  owa_util.http_header_close;
  wpg_docload.download_file(l_final_zip);

  apex_application.stop_apex_engine;

END;

📌 Tip: In Oracle APEX, you can use this code inside a PL/SQL process on a button or page.

---

Advanced Example: Zipping BLOB Files Stored in a Table

If you store files such as images, PDFs, or Word documents in a table as BLOBs, you can add them to the ZIP dynamically:

DECLARE
  l_zip BLOB;
  l_final_zip BLOB;
BEGIN
  APEX_ZIP.INIT(l_zip);

  FOR file_rec IN (
    SELECT file_name, file_content
    FROM my_files_table
    WHERE user_id = :APP_USER
  ) LOOP
    APEX_ZIP.ADD_FILE(
      p_zipped_blob => l_zip,
      p_file_name   => file_rec.file_name,
      p_content     => file_rec.file_content);
  END LOOP;

  l_final_zip := APEX_ZIP.FINISH(l_zip);

  owa_util.mime_header('application/zip', FALSE);
  htp.p('Content-Disposition: attachment; filename="myfiles.zip"');
  owa_util.http_header_close;
  wpg_docload.download_file(l_final_zip);

  apex_application.stop_apex_engine;

END;

---

Important Notes and Common Pitfalls

---

Conclusion

The APEX_ZIP package is a powerful tool for file compression in Oracle APEX. With it, you can:

• Generate ZIP files on the fly for reports, attachments, and exports.

• Deliver multi-file downloads easily.

• Compress both text and binary content from the database or dynamic sources.

Since it’s a built-in package, you can use it in any APEX environment without installing extra components or third-party libraries.

---

Have Questions?

If you’ve used this package or faced issues while implementing it, I’d love to hear from you! Feel free to comment or reach out via LinkedIn.