• Post author:
  • Post category:PLSQL
  • Reading time:4 mins read

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';
  1. 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.

zip a file in PL/SQL

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:

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.in and orclqa.com a question and answer forum for developers.

This Post Has 3 Comments

  1. Md.Aslam Hossain

    How run pdf report & send email from oracle database automatically daily

  2. Zameer

    Hi Team,

    Upon following the same steps using the package zip_util_pkg, I could zip the files (3 PDF files) and it's size is 127k (of course as you know we can not predict the size) where as when zipped only one PDF file and could able to send zip file of size 19k.

    Then after when I tried to send the zip file of size 127k using utl_mail.send_attach_raw, I am getting the below error:
    [Error] Execution (1: 1): ORA-06525: Length Mismatch for CHAR or RAW data
    ORA-06512: at "SYS.UTL_FILE", line 127
    ORA-06512: at "SYS.UTL_FILE", line 1204
    ORA-06512: at line 29

  3. Zameer

    DECLARE
      vinhandle UTL_FILE.file_type;
      rfile    RAW(32767);      --change the size accordingly
      flen    NUMBER;
      filename varchar2(100);
      bsize    NUMBER;
      ex     BOOLEAN;
      vrec    VARCHAR2 (30)   := 'receiver@abc.com';   --receipant email id
      vsender   VARCHAR2 (30)   := 'sender@abc.com';    --sender's email id 
      vsubj    VARCHAR2 (50)   := 'Testing attached email';    --subject  
      vmesg    VARCHAR2 (4000);
      vmtype   VARCHAR2 (30)   := 'text/plain; charset=us-ascii';
    lcl_data_path       VARCHAR2(300);
    BEGIN
      lcl_data_path:='SHIPPING_DOC_DIR_RNO'; -- Directory of the server path where in files and zip file exist.
      filename := 'SO013804637.zip';
      vmesg := 'Please find the file attachment';
      dbms_output.put_line(lcl_data_path);
      vinhandle := UTL_FILE.fopen (lcl_data_path, 'SO013804637.zip', 'rb');
      UTL_FILE.fgetattr (lcl_data_path, 'SO013804637.zip', ex, flen, bsize);
     UTL_FILE.get_raw (vinhandle, rfile, flen);
      UTL_FILE.fclose (vinhandle);
      utl_mail.send_attach_raw (sender      => vsender,
                   recipients    => vrec,
                   subject      => vsubj,
                   MESSAGE      => vmesg,
                   attachment    => rfile,
                   att_inline    => FALSE,
                   att_filename   => filename
                  );
                  dbms_output.put_line('end');
    END;

Comments are closed.