Read and Store all Files Contained in the ZIP File into Oracle Table using Oracle APEX API

Here I am giving an example to to read and store all files contained in the ZIP into oracle table using a simple oracle APEX API.

APEX_ZIP: This package manages the zipping and unzipping of files.

Data Types

The data types used by the APEX_ZIP package are described in this section.

t_files

type t_files is table of varchar2 (32767) index by binary_integer;

----------------------------------------------------------------------------------------------------------------------------

GET_FILE_CONTENT Function

This function returns the BLOB of a file contained in a provided zip file.

Syntax:

apex_zip.get_file_content ( p_zipped_blob IN BLOB, 

                            p_file_name   IN VARCHAR2, 

                            p_encoding    IN VARCHAR2 DEFAULT NULL)

RETURN BLOB;

----------------------------------------------------------------------------------------------------------------------------

GET_FILES Function

This function returns an array of file names, including the path, of a provided zip file that contains a BLOB.

Syntax:

apex_zip.get_files ( p_zipped_blob IN BLOB, 

                     p_only_files  IN boolean DEFAULT TRUE, 

                     p_encoding    IN VARCHAR2 DEFAULT NULL)

RETURN t_files;

----------------------------------------------------------------------------------------------------------------------------

Step 1: Create a table fxgn_zip_files, which will be used to store ZIP files.

-- Create TABLE

CREATE TABLE fxgn_zip_files

  (

    file_id   NUMBER,

    file_name VARCHAR2(240),

    mime_type VARCHAR2(240),

    raw_document BLOB,

    image_last_update TIMESTAMP (6),

    updated_by        VARCHAR2(240)

  );

-- Create SEQUENCE

CREATE SEQUENCE fxgn_zip_files_s START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE fxgn_zip_files_s START WITH 1 INCREMENT BY 1;

Step 2: Create a table fxgn_files, which will be used to store all files contained in a ZIP file.

-- Create TABLE

CREATE TABLE fxgn_files

  (

    file_id   NUMBER,

    file_name VARCHAR2(240),

    mime_type VARCHAR2(240),

    raw_document BLOB,

    image_last_update TIMESTAMP (6),

    updated_by        VARCHAR2(240),

    parent_file_id    NUMBER

  );

  

-- Create SEQUENCE  

CREATE SEQUENCE fxgn_files_s START WITH 1 INCREMENT BY 1; 

Step 3: Create a new blank page.

Note: Mine was page 17. You will need to update reference to "P17" with your page number if it's different.

Step 4: Create a new HTML region to the page.

Step 5: Create a new item to the region. Set name to P17_FILE_UPLOAD and change the type File Browser.

Step 6: Create two new buttons. Set name to ZIP_UPLOAD,  EXTRACT_FILES and leave the action default of Submit Page and set server side condition Never.

Step 7: Create a PLSQL process with below script, it should be executed when you click ZIP_UPLOAD button and set name to Upload Files. 

-- Script to upload ZIP File

DECLARE

  l_blob BLOB;

  l_mime_type VARCHAR2(2000);

BEGIN

  SELECT blob_content,

         mime_type

    INTO l_blob,

         l_mime_type

    FROM apex_application_temp_files

   WHERE NAME = :p17_file_upload;

   

  INSERT

  INTO fxgn_zip_files

    (

      file_id,

      file_name,

      mime_type,

      raw_document,

      image_last_update,

      updated_by

    )

    VALUES

    (

      fxgn_zip_files_s.nextval,

      substr (:p17_file_upload,instr(:p17_file_upload,'/',1)+1),

      l_mime_type,

      l_blob,

      SYSTIMESTAMP,

      :app_username

    );

END;

/

Step 8: Create a new dynamic action that fires when the file is upload. The easiest way to do this is to right click the button and select Create Dynamic Action Set name to Upload ZIP File

Step 9: After the file is uploaded, same would be reflecting in fxgn_zip_files table. Post the upload the same will be reflecting in Zipped Files (classical report) section. Create classical report to show uploaded ZIP files as below,

Step 10: Provide option to view uploaded files by clicking on the report (zipped file) attribute File. [Optional]

Step 11: Create a PLSQL process with below script, it should be executed when you click EXTRACT_FILES button and set name to Extract Files. 

-- Script to read a zip file from fxgn_zip_files,

-- extracting it and storing all files of the zip file into fxgn_files.

DECLARE

  l_zip_file BLOB;

  l_unzipped_file BLOB;

  l_files apex_zip.t_files;

  l_mime_type VARCHAR2(2000) := 'application/pdf';

BEGIN

  DELETE FROM fxgn_files WHERE file_id = :p17_file_id;

  SELECT raw_document

    INTO l_zip_file

    FROM fxgn_zip_files

   WHERE file_id = :p17_file_id;

   

  l_files      := apex_zip.get_files ( p_zipped_blob => l_zip_file );

  

  FOR i IN 1 .. l_files.count

  loop

    l_unzipped_file := apex_zip.get_file_content ( p_zipped_blob => l_zip_file, 

                                                   p_file_name => l_files(i));

    INSERT

    INTO fxgn_files

      (

        parent_file_id,

        file_id,

        file_name,

        raw_document,

        mime_type,

        image_last_update

      )

      VALUES

      (

        :p17_file_id,

        fxgn_files_s.nextval,

        l_files(i),

        l_unzipped_file,

        l_mime_type,

        LOCALTIMESTAMP

      );

  END loop;

END;

/

Step 12: Create a new item to the region. Set name to P17_FILE_ID and change the type Hidden.

Step 13: Read a zip file from a fxgn_zip_files table, extracting it and storing all files of the zip file into fxgn_files. 

Change UNZIP column type to Link and Set target type URL and copy and paste below JS code in URL section as below, (zipped files - classical report)

URL: javascript:$s('P17_FILE_ID','#FILE_ID#'); javascript:apex.confirm ('Are you sure you want to extract files from this folder?', 'EXTRACT_FILES');

Step 14: After the file is extracted, same would be reflecting in fxgn_files table. Post the upload the same will be reflecting in Extracted Zip Files (classical report) section. Create classical report to show all the extracted files in a provided zip file as below,

Step 15: Provide option to view extracted files by clicking on the report (extracted zip files) attribute File. [Optional]

The demo is here.

Review Mechanism:

Step 1: Select a file/Drag and drop the file from the location.

Step 2: After the file is uploaded, same would be reflecting in fxgn_zip_files table.

Step 3: Post the upload the same would be reflecting in Zipped Files section.

Step 4: The same could be reviewed by clicking on the report (zipped file) attribute File

Step 4: Zipped file can be opened by clicking on icon  and the same would be reflecting in fxgn_files.

Step 5: User can see all the extracted files in the zip folder would reflect in the Extracted Zip Files section.

Step 6: The same could be reviewed by clicking on the report (extracted zip files) attribute File.

That's it.

Happy APEXing!!!...

References/Credits:

• [APEX_ZIP] Application Express API Reference