How to UNZIP a File in PL/SQL?

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • PLSQL
  • 2 mins read

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
  1. 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.
  2. 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:

This Post Has 3 Comments

  1. Abdullah Zabarah July 12, 2021

    Thanks, Vinish, really helpful content from a helpful person.

  2. Shrikant Gokhale October 6, 2021

    This is good, only thing is that it takes lot of time to unzip files. for 6290 files it took 1 hr to unzip all files which is too much. I have a client where there are 300000 files to be unzipped. Imagine how much time it will take !!

Comments are closed.