While working on something today, I wanted to unzip a file using APEX_ZIP – and found that the old method was deprecated. There is now a completely new one, quite a bit different. So, let’s take a closer look at it and show some examples of its use.
For this demonstration, I’ve zipped up the apex\utilities folder from the APEX download site, which contains these files:
The ZIP file looks like this:
I’ve uploaded the ZIP file to this table:
CREATE TABLE "MY_FILES"
( "ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
"MY_FILE" BLOB,
"MY_FILE_FILENAME" VARCHAR2(255 CHAR),
"MY_FILE_MIMETYPE" VARCHAR2(255 CHAR),
"MY_FILE_CHARSET" VARCHAR2(255 CHAR),
"MY_FILE_LASTUPD" DATE,
CONSTRAINT "MY_FILES_ID_PK" PRIMARY KEY ("ID")
USING INDEX ENABLE
) ;
Great. We’re all set.
Notice that the APEX 23.2 documentation shows this:
However, the APEX 24.1 documentation shows something different:
What these pictures are telling me is that the usual way of getting the list of files (GET_FILES) AND the method of getting a particular file (GET_FILE_CONTENT signature 1) are both deprecated! Oh no, we are not doomed! Thankfully, there is a new method in town!
Let’s take a look at how unzipping works in the current version of APEX.
If you are browsing the ZIP file, perhaps you are looking for particular files matching a regex or similar, then consider this code:
DECLARE
l_zip_file blob;
l_unzipped_file blob;
l_dir apex_zip.t_dir_entries;
l_file_path varchar2(32767);
l_current_entry apex_zip.t_dir_entry;
BEGIN
-- Retrieve the zip file from the database
SELECT my_file
INTO l_zip_file
FROM my_files
WHERE my_file_filename = 'utilities.zip';
-- Get all directory entries from the zip file
l_dir := apex_zip.get_dir_entries (
p_zipped_blob => l_zip_file,
p_only_files => FALSE );
-- Print header row for the output table
DBMS_OUTPUT.PUT_LINE(RPAD('Dir', 5) || ' ' ||
RPAD('Size', 5) || ' ' ||
RPAD('Filename', 35) || ' ' ||
RPAD('Index', 35) || ' ' ||
'Uncompressed-Size');
-- Print separator line
DBMS_OUTPUT.PUT_LINE(RPAD('-', 101, '-'));
-- Start with the first entry in the directory
l_file_path := l_dir.first;
-- Loop through all entries in the zip file
WHILE l_file_path IS NOT NULL LOOP
-- Initialize unzipped file as empty BLOB
l_unzipped_file := EMPTY_BLOB();
-- Get current directory entry
l_current_entry := l_dir(l_file_path);
-- If the entry is not a directory, extract its content
IF l_current_entry.is_directory IS NULL THEN
l_unzipped_file := apex_zip.get_file_content (
p_zipped_blob => l_zip_file,
p_dir_entry => l_current_entry );
END IF;
-- Print information about the current entry
DBMS_OUTPUT.PUT_LINE(
apex_string.format( '%0 %1 %2 %3 %4' ,
RPAD(apex_debug.tochar(l_current_entry.is_directory), 5),
LPAD(l_current_entry.uncompressed_length, 5),
RPAD(l_current_entry.file_name, 35),
RPAD(l_file_path, 35),
LPAD(LENGTH(l_unzipped_file), 5)
));
-- Move to the next entry in the directory
l_file_path := l_dir.next(l_file_path);
END LOOP;
END;
This produces the following:
Dir Size Filename Index Uncompressed-Size ----------------------------------------------------------------------------------------------------- null 28046 apxpart.sql apxpart.sql 28046 null 1815 apxrekey.sql apxrekey.sql 1815 null 4601 check_lovs_for_errors.sql check_lovs_for_errors.sql 4601 true 0 debug/ debug/ 0 null 12615 debug/activity.sql debug/activity.sql 12615 null 2724 debug/d0.sql debug/d0.sql 2724 null 2870 debug/d1.sql debug/d1.sql 2870 null 3112 debug/d2.sql debug/d2.sql 3112 null 2797 debug/ds.sql debug/ds.sql 2797 null 4683 enable_sso.sql enable_sso.sql 4683 null 15746 report_instance_configuration.sql report_instance_configuration.sql 15746 null 1782 reset_image_prefix.sql reset_image_prefix.sql 1782 null 2772 reset_image_prefix_con.sql reset_image_prefix_con.sql 2772 null 2642 reset_image_prefix_core.sql reset_image_prefix_core.sql 2642 true 0 support/ support/ 0 null 33979 support/apex_verify.sql support/apex_verify.sql 33979
Here’s what we can observe here:
l_unzipped_file := apex_zip.get_file_content (
p_zipped_blob => l_zip_file,
p_dir_entry => l_current_entry );
If you are looking for a particular file, it becomes way easier:
DECLARE
l_zip_file blob;
l_unzipped_file blob;
l_dir apex_zip.t_dir_entries;
l_file_path varchar2(32767);
l_current_entry apex_zip.t_dir_entry;
BEGIN
-- Retrieve the zip file from the database
SELECT my_file
INTO l_zip_file
FROM my_files
WHERE my_file_filename = 'utilities.zip';
-- Get all directory entries from the zip file
l_dir := apex_zip.get_dir_entries (
p_zipped_blob => l_zip_file );
-- Print header row for the output table
DBMS_OUTPUT.PUT_LINE(RPAD('Dir', 5) || ' ' ||
RPAD('Size', 5) || ' ' ||
RPAD('Filename', 35) || ' ' ||
RPAD('Index', 35) || ' ' ||
'Uncompressed-Size');
-- Print separator line
DBMS_OUTPUT.PUT_LINE(RPAD('-', 101, '-'));
l_file_path := 'support/apex_verify.sql';
l_current_entry := l_dir( l_file_path );
l_unzipped_file := apex_zip.get_file_content (
p_zipped_blob => l_zip_file,
p_dir_entry => l_current_entry );
-- Print information about the current entry
DBMS_OUTPUT.PUT_LINE(
apex_string.format( '%0 %1 %2 %3 %4' ,
RPAD(apex_debug.tochar(l_current_entry.is_directory), 5),
LPAD(l_current_entry.uncompressed_length, 5),
RPAD(l_current_entry.file_name, 35),
RPAD(l_file_path, 35),
LPAD(LENGTH(l_unzipped_file), 5)
));
END;
This produces the following:
Dir Size Filename Index Uncompressed-Size ----------------------------------------------------------------------------------------------------- null 33979 support/apex_verify.sql support/apex_verify.sql 33979
There’s also something we can observe from this:
l_file_path := 'support/apex_verify.sql';
l_current_entry := l_dir( l_file_path );
l_unzipped_file := apex_zip.get_file_content (
p_zipped_blob => l_zip_file,
p_dir_entry => l_current_entry );
And that’s about it for today! I hope you found this quick analysis informative and helpful. This piece couldn’t exist without the following sources:
Also, if you’re interested in APEX-related content, check out some of my other articles on this blog:
