How to UNZIP a File in PL/SQL?

In my previous post, I have given an example to ZIP a file using PL/SQL in Oracle, and in this post, I am explaining to how to extract a ZIP file, or unzip a file in PL/SQL using the same Alexandria PL/SQL utility package.

Follow These Steps to Unzip (Extract) a ZIP File Using PL/SQL in Oracle

1. Click on the Download the Alexandria PL/SQL Utility Package to download the package first.
2. Then extract the ZIP file and locate the below mentioned four files in the alexandria-plsql-utils-master\ora\ directory:

• zip_util_pkg.pks
• zip_util_pkg.pkb
• file_util_pkg.pks
• file_util_pkg.pkb

3. Install these scripts in your Oracle Database Schema and while installing if there is an error due to missing object, then find in the alexandria-plsql-utils-master\extras directory and install it.
4. Now create a directory object in Oracle for your ZIP files location, as shown in below example.

Create OR Replace Directory UNZIP_FILES as 'c:\my_zip_files';

You can now extract a ZIP file using PL/SQL, as shown in below example.

Unzip a File in PL/SQL Example

DECLARE
fl zip_util_pkg.t_file_list;
l_file BLOB;
BEGIN
/* specify the directory object and your ZIP file to get_file_list function */
fl := zip_util_pkg.get_file_list ('UNZIP_FILES', 'plsql_3.zip');

IF fl.COUNT () > 0
THEN
FOR i IN fl.FIRST .. fl.LAST
LOOP

l_file := zip_util_pkg.GET_FILE ('UNZIP_FILES', 'plsql_3.zip', fl (i));

file_util_pkg.save_blob_to_file ('UNZIP_FILES', fl (i), l_file);
END LOOP;
END IF;
END;
/

Check the folder location for UNZIP_FILES directory object, and you will find the extracted files.

See also:

• Export Data to Excel File in Oracle
• Create PDF Report Using PL/SQL