8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

APEX_ZIP : Manage Zip Files From PL/SQL

The APEX_ZIP package provides an API to manage zip files from PL/SQL. It's installed as part of APEX, but it is available from PL/SQL, so you don't need to be using APEX to use the package.

Setup
GET_FILES Procedure : List File and Directory Names in a Zip File
GET_FILE_CONTENT Function : Retrieve Files From a Zip File
ADD_FILE and FINISH Procedures : Create a New Zip File
Comments

Setup

Create a directory object and make sure the test user has read/write permissions on it.

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

create or replace directory tmp_dir as '/tmp/';
grant read, write on directory tmp_dir to testuser1;

Connect back to the test user and compile the file_to_blob and blob_to_file procedures.

conn testuser1/testuser1@//localhost:1521/pdb1

@https://oracle-base.com/dba/miscellaneous/file_to_blob.sql
@https://oracle-base.com/dba/miscellaneous/blob_to_file.sql

Create a table to hold files in BLOB columns.

# drop table media purge;

create table media (
  id             number generated always as identity,
  content_type   varchar2(100)  not null,
  file_name      varchar2(100)  not null,
  content        blob           not null,
  constraint media_pk primary key (id),
  constraint media_uk unique (file_name)
);

Get a zip file of your choice and put it on the "/tmp" location on the database server. In this example we are using a file called "test_file.zip", which contains PNG files in subdirectories, and includes an empty subdirectory.

We are now ready to start using the the APEX_ZIP package.

GET_FILES Procedure : List File and Directory Names in a Zip File

The GET_FILES function returns the names of the files and directories in the zip file.

The following example loads the zip file into a BLOB, then uses the GET_FILES procedure to retrieve the list of file and directory names into a T_FILES collection. It then loops through the collection displaying the file names.

set serveroutput on
declare
  l_zip    blob;
  l_files  apex_zip.t_files;
begin
  dbms_lob.createtemporary(l_zip, false);

  -- Load the zip file into a BLOB.
  file_to_blob(p_blob     => l_zip,
               p_dir      => 'TMP_DIR',
               p_filename => 'test_file.zip');

  -- Get the list of files.
  l_files := apex_zip.get_files(p_zipped_blob => l_zip);

  -- Loop through the files and list the names.
  for i in 1 .. l_files.count loop
    dbms_output.put_line(i || ' : ' || l_files(i));
  end loop;
  
  dbms_lob.freetemporary(l_zip); 
exception
  when others then
    dbms_lob.freetemporary(l_zip);
    raise;
end;
/
1 : clone/multitenant-clone-2.png
2 : clone/multitenant-create-from-seed-2.png
3 : clone/multitenant-remote-clone-2.png
4 : unplug/multitenant-unplug-plug-2.png


PL/SQL procedure successfully completed.

SQL>

By default the subdirectory directory names are not included in the list. Setting the P_ONLY_FILES parameter to FALSE includes them in the list.

set serveroutput on
declare
  l_zip    blob;
  l_files  apex_zip.t_files;
begin
  dbms_lob.createtemporary(l_zip, false);

  -- Load the zip file into a BLOB.
  file_to_blob(p_blob     => l_zip,
               p_dir      => 'TMP_DIR',
               p_filename => 'test_file.zip');

  -- Get the list of files.
  l_files := apex_zip.get_files(p_zipped_blob => l_zip,
                                p_only_files  => false);

  -- Loop through the files and list the names.
  for i in 1 .. l_files.count loop
    dbms_output.put_line(i || ' : ' || l_files(i));
  end loop;
  
  dbms_lob.freetemporary(l_zip); 
exception
  when others then
    dbms_lob.freetemporary(l_zip);
    raise;
end;
/
1 : clone/
2 : clone/multitenant-clone-2.png
3 : clone/multitenant-create-from-seed-2.png
4 : clone/multitenant-remote-clone-2.png
5 : empty_dir/
6 : unplug/
7 : unplug/multitenant-unplug-plug-2.png


PL/SQL procedure successfully completed.

SQL>

GET_FILE_CONTENT Function : Retrieve Files From a Zip File

The GET_FILE_CONTENT function returns the file from the zip file as a BLOB.

We check the contents of the MEDIA table.

select count(*) from media;

  COUNT(*)
----------
         0

SQL>

This example builds on the first one. Instead of displaying the file name it uses the GET_FILE_CONTENT function to return the file as a BLOB, then inserts it into the MEDIA table.

declare
  l_zip          blob;
  l_files        apex_zip.t_files;
  l_file         blob;
begin
  dbms_lob.createtemporary(l_zip, false);

  -- Load the zip file into a BLOB.
  file_to_blob(p_blob     => l_zip,
               p_dir      => 'TMP_DIR',
               p_filename => 'test_file.zip');

  -- Get the list of files.
  l_files := apex_zip.get_files(p_zipped_blob => l_zip);

  -- Loop through the files and list the names.
  for i in 1 .. l_files.count loop
    l_file := apex_zip.get_file_content(p_zipped_blob => l_zip,
                                        p_file_name   => l_files(i));
    
    insert into media (content_type, file_name, content)
    values ('image/png', l_files(i), l_file);
  end loop;
  commit;
  
  dbms_lob.freetemporary(l_zip); 
exception
  when others then
    dbms_lob.freetemporary(l_zip);
    raise;
end;
/

In this example we know the files in the zip are PNG files, so we're just hard-coding the content type. In a real situation we would have to infer the content type based on the file extension. We are also not stripping the directories from the file names.

Check the contents of the MEDIA table.

column file_name format a40

select id, file_name, length(content) as content_length
from   media;

        ID FILE_NAME                                CONTENT_LENGTH
---------- ---------------------------------------- --------------
         1 clone/multitenant-clone-2.png                      7614
         2 clone/multitenant-create-from-seed-2.png           7222
         3 clone/multitenant-remote-clone-2.png               6192
         4 unplug/multitenant-unplug-plug-2.png               6996

SQL>

ADD_FILE and FINISH Procedures : Create a New Zip File

The ADD_FILE procedure adds a new file to a zip file in a BLOB. It can be called multiple times, and once the last file is added, the FINISH procedure closes off the zip file.

The following example adds all the files from the media table to a new zip file and writes it out to the file system with the name "test_file_2.zip".

declare
  l_zip  blob;
begin
  -- Add the file to a new zip.
  for cur_rec in (select file_name,
                         content
                  from   media)
  loop
    apex_zip.add_file(p_zipped_blob => l_zip,
                      p_file_name   => cur_rec.file_name,
                      p_content     => cur_rec.content);
  end loop;

  -- Close the zip file.
  apex_zip.finish(p_zipped_blob => l_zip);

  -- Write the file to the file system.
  blob_to_file(p_blob     => l_zip,
               p_dir      => 'TMP_DIR',
               p_filename => 'test_file_2.zip');
end;
/

We can check the contents of the "test_file_2.zip" file using the GET_FILES function.

set serveroutput on
declare
  l_zip    blob;
  l_files  apex_zip.t_files;
begin
  dbms_lob.createtemporary(l_zip, false);

  -- Load the zip file into a BLOB.
  file_to_blob(p_blob     => l_zip,
               p_dir      => 'TMP_DIR',
               p_filename => 'test_file_2.zip');

  -- Get the list of files.
  l_files := apex_zip.get_files(p_zipped_blob => l_zip);

  -- Loop through the files and list the names.
  for i in 1 .. l_files.count loop
    dbms_output.put_line(i || ' : ' || l_files(i));
  end loop;
  
  dbms_lob.freetemporary(l_zip); 
exception
  when others then
    dbms_lob.freetemporary(l_zip);
    raise;
end;
/
1 : clone/multitenant-clone-2.png
2 : clone/multitenant-create-from-seed-2.png
3 : clone/multitenant-remote-clone-2.png
4 : unplug/multitenant-unplug-plug-2.png


PL/SQL procedure successfully completed.

SQL>

Comments

Here are some comments about the APEX_ZIP package.

Once the FINISH procedure is called, you cannot write any more files to the zip.
If you load an existing zip and attempt to use the add new files using the ADD_FILE procedure, the operation will fail silently, and the new files will not be added. To add new files to an existing zip you must transfer the contents of the existing zip to a new zip, add the new files, then close the new zip.
There is no functionality to delete a file from an existing zip. To achieve this you need to create a new zip and transfer the files you want to keep to it.
The UTL_COMPRESS package uses the gzip format, but it can only handle a single file, rather than a collection of files in the archive. Depending on your use case, either APEX_ZIP or UTL_COMPRESS could be useful.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.