8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 24ai | 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

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.