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:

A screenshot showin the folder.

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:

A screenshot from the documentation.

However, the APEX 24.1 documentation shows something different:

A screenshot from the documentation.

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:

Also, if you’re interested in APEX-related content, check out some of my other articles on this blog:

  1. SQLcl Project Reference: A summary of everything you need to know
  2. APEX User Interface Defaults: A deep dive into Table Dictionary and Attribute Dictionary
  3. Dracula Theme for Oracle APEX Page Designer: A quick tutorial
  4. Sorting in APEX: A deep dive into Classic Reports and Card Regions
  5. Globalization in APEX: A deep dive into Multi-Language and Locales
Share