How to ZIP a File in PL/SQL?

In this blog post, I am giving an example to ZIP a file in PL/SQL. I am using Alexandria PL/SQL Util Library to perform this task. The same library I used for the example of export data to Excel from Oracle Database table. Follow these steps to create a ZIP file using PL/SQL in Oracle.

Create a ZIP File in Oracle Using PL/SQL

1. First, download the Alexandria PL/SQL library from the Github using the following link Download.
2. After downloading the file, extract it and locate the zip_util_pkg.pks and zip_util_pkg.pkb files in the \alexandria-plsql-utils-master\ora\ directory and execute these scripts in your Oracle Database Schema to install it.
3. If any supported objects are needed, then find in the folder \alexandria-plsql-utils-master\ and install it.
4. You have installed the utility package and related objects. Now create a directory object in your schema, as shown in below example.

Create OR Replace Directory zip_files as 'c:\zip_files';

5. Then create below function in the same Schema to convert a file to a BLOB. This function will be used to create a Zip file using PL/SQL zip_util_pkg package. Also note, that before creating this function change the ZIP_FILES directory name with your database directory object you created above.

CREATE OR REPLACE FUNCTION file_to_blob (p_file_name VARCHAR2)
RETURN BLOB
AS
dest_loc BLOB := EMPTY_BLOB ();
src_loc BFILE := BFILENAME ('ZIP_FILES', p_file_name);
BEGIN
DBMS_LOB.OPEN (src_loc, DBMS_LOB.LOB_READONLY);

DBMS_LOB.CREATETEMPORARY (lob_loc => dest_loc,
cache => TRUE,
dur => DBMS_LOB.session);

DBMS_LOB.OPEN (dest_loc, DBMS_LOB.LOB_READWRITE);

DBMS_LOB.LOADFROMFILE (dest_lob => dest_loc,
src_lob => src_loc,
amount => DBMS_LOB.getLength (src_loc));

DBMS_LOB.CLOSE (dest_loc);
DBMS_LOB.CLOSE (src_loc);

RETURN dest_loc;
END file_to_blob;
/

Test

Note: In the following example, file emp.dat must exist in the ZIP_FILES directory we created above.

DECLARE
l_file1 BLOB;
l_zip BLOB;
BEGIN

/* emp.dat should be in ZIP_FILES directory location */
l_file1 := file_to_blob ('emp.dat');

zip_util_pkg.add_file (l_zip, 'emp.dat', l_file1);

zip_util_pkg.finish_zip (l_zip);

/* it will create the zip file named plsql_1.zip in the ZIP_FILES directory */

zip_util_pkg.save_zip (l_zip, 'ZIP_FILES', 'plsql_1.zip');
END;
/

You can now check the location of ZIP_FILES directory for the plsql_1.zip file.

To ZIP Multiple Files

DECLARE
l_file1 BLOB;
l_file2 BLOB;
l_zip BLOB;
BEGIN
l_file1 := file_to_blob ('emp.dat');
l_file2 := file_to_blob ('scott.sql');

zip_util_pkg.add_file (l_zip, 'emp.dat', l_file1);
zip_util_pkg.add_file (l_zip, 'scott.sql', l_file2);

zip_util_pkg.finish_zip (l_zip);

zip_util_pkg.save_zip (l_zip, 'ZIP_FILES', 'plsql_3.zip');
END;
/

See also:

• Unzip a File in PL/SQL
• Create JSON File in Oracle 11g Using PL/SQL
• Export Data From Oracle SQL Developer to Excel
• Create PDF Files Using PL/SQL