Contents
Learn about the recent changes in the unzipping method used in Oracle APEX – with in-depth examples and observations.
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.
Preparation
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.
Documentation
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!
Unzipping files
Let’s take a look at how unzipping works in the current version of APEX.
Browsing the ZIP file?
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:
- The presence of the Uncompressed-Size means that it’s been unzipped, and the fact that it’s identical to the size means it’s been unzipped correctly.
- apex_zip.get_dir_entries returns an object of type t_dir_entries which is a table of t_dir_entry index by VARCHAR2(32767). What is the index? It’s the filename! This is why I can grab the filename by either of these two:
- l_file_path (index) ← shorter and easier!
- l_current_entry.file_name (attribute)
- If the index were a BINARY_INTEGER we could do a FOR..LOOP around it. However, it’s a VARCHAR2 – therefore we need to LOOP around it using a WHILE l_file_path IS NOT NULL.
- If I want to grab the file, I have to set the whole record type that I fetch in each loop. This is stored as l_dir(l_file_path) and is used as an in parameter for apex_zip.get_file_content.
l_unzipped_file := apex_zip.get_file_content (
p_zipped_blob => l_zip_file,
p_dir_entry => l_current_entry );
- l_current_entry.is_directory is true when apex_zip.get_dir_entries is called with p_only_files => FALSE. This means that I have to use IF l_current_entry.is_directory IS NULL THEN to detect files. Alternatively, you can skip the p_only_files as this defaults to TRUE. However, the issue with this is that l_current_entry.is_directory will only ever be null. It cannot be FALSE.
Unzipping a particular file?
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:
- No looping! We still need to call apex_zip.get_dir_entries, which gives us our collection. We can then identify which record in the collection by the filename and then pass it into apex_zip.get_file_content like below.
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 );
Conclusion
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:
- Oracle APEX ZIP API – an Upgrade which is not officially documented – a great blog post by Zoran Tica
- apex_zip.get_dir_entries – official Oracle documentation
Also, if you’re interested in APEX-related content, check out some of my other articles on this blog:
- SQLcl Project Reference: A summary of everything you need to know
- APEX User Interface Defaults: A deep dive into Table Dictionary and Attribute Dictionary
- Dracula Theme for Oracle APEX Page Designer: A quick tutorial
- Sorting in APEX: A deep dive into Classic Reports and Card Regions
- Globalization in APEX: A deep dive into Multi-Language and Locales