Unzipping in Oracle APEX has changed - Examples Galore

I wanted to unzip a file using APEX_ZIP today, and found that the old method was deprecated and now there is a completely different method. Lets unpack it all with examples aplenty.

Preparation

For this demonstration, I’ve decided to zip up the apex\utilities folder, from the APEX download site which contains these files:

Giving a ZIP file looking like this:

I’ve uploaded the zip it 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 this:

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!

No we are not doomed. There is a new method in town.

Unzipping files

Browsing the zip?

If you are browsing the zip, 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:

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

We can observe that:

• The presence of the Uncompressed-Size means that its been unzipped & the fact that its identical to the size means its been correctly unzipped

• 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? - its the filename! This is why I can grab the filename by either of these two:

  • l_file_path (index) ← shorter & easier!

  • l_current_entry.file_name (attribute)

• If the index was a BINARY_INTEGER we could do a FOR..LOOP around it. However its 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 it the whole record type which I fetch on each loop. This is stored as l_dir(l_file_path) and is used as an in parameter to 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 had to use IF l_current_entry.is_directory IS NULL THEN to detect files. Alternatively you can omit the p_only_files as this defaults to TRUE; the issue with this is that l_current_entry.is_directory will only ever be null. It cannot be FALSE - source.

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

Dir   Size  Filename                            Index                               Uncompressed-Size
-----------------------------------------------------------------------------------------------------
null  33979 support/apex_verify.sql             support/apex_verify.sql             33979

We can observe that:

• 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 i.e.

        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 );
  

ENJOY

What’s the picture? It’s Cross James St. Harrogate. Visit Yorkshire!

Credits

• Oracle APEX ZIP API - an Upgrade which is not officially documented - Zoran Tica

• apex_zip.get_dir_entries - Oracle Documentation