Introduction

As you may know, Oracle APEX 24.1 is now generally available, and I started to spot the new features. One of them is the improvement in file downloads with a brand new dynamic action, page process type and a PL/SQL API! Let's take a look at that.

New `apex_http` PL/SQL API

There were a couple of possibilities to download a file using an Oracle APEX page process, it usually combine the use of the owa_util, htp, wpg_docload and apex_application packages:

declare
    l_filename     my_file.filename%type;
    l_mime_type    my_file.mime_type%type;
    l_blob_content my_file.blob_content%type;
begin
   select filename, mime_type, blob_content
     into l_filename, l_mime_type, l_blob_content 
     from my_files
    where id = :PX_ID;
  --initialize headers
  sys.htp.init;
  sys.owa_util.mime_header(l_mime_type, false);
  sys.htp.p('Content-Length: ' || sys.dbms_lob.getlength(l_blob_content));
  sys.htp.p('Content-Disposition: attachment; filename="' || l_filename || '"');
  sys.owa_util.http_header_close;
  --Download File
  sys.wpg_docload.download_file(l_blob_content);
  --Stop APEX page processing
  apex_application.stop_apex_engine;
end;

We all have this snippet somewhere in our files but starting from 24.1, this code become much simpler with the use of the new apex_http package

declare
    l_filename     my_file.filename%type;
    l_mime_type    my_file.mime_type%type;
    l_blob_content my_file.blob_content%type;
begin
   select filename, mime_type, blob_content
     into l_filename, l_mime_type, l_blob_content 
     from my_files
    where id = :PX_ID;

   apex_http.download(
        p_blob         => l_blob_content,
      , p_content_type => l_mime_type,
      , p_filename     => l_filename
   );
end;

I really like this new API, and there is another signature that let you download clob as file

declare
    l_filename     my_file.filename%type;
    l_mime_type    my_file.mime_type%type;
    l_clob_content my_file.clob_content%type;
begin
   select filename, mime_type, clob_content
     into l_filename, l_mime_type, l_clob_content 
     from my_files
    where id = :PX_ID;

   apex_http.download(
        p_clob         => l_clob_content,
      , p_content_type => l_mime_type,
      , p_filename     => l_filename 
   );
end;

New Download dynamic action

But the real new feature of this release regarding download is the brand new Download dynamic action. This DA will let you download a file based on a SQL query, but it also have the ability to download multiple files at once as an zip archive.

Configuration to download a single file

Create a dynamic action and configure the following attribute

Action: Download
Multiple Files: Disabled
View File As: Attachment
SQL Query: Provide a valid SQL Query returning the file content, the file name and the mime type in this order

A configuration interface showing sections for Identification, Settings, and Source. Identification includes fields for Name and Action (set to "Download"). Settings include options for selecting multiple files (toggle off) and viewing files as attachments. Source displays an SQL query for selecting content from a database table and an item labeled "Items to Submit" with a value "P1_ID".

Configuration to download multiple files

The configuration in that case is very similar, but the Multiple Files attribute is enable and a new attribute is then available to specify the name of the archive

Action: Download
Multiple Files: Enabled
Filename: Name of the archive (ex: my_files.zip)
View File As: Attachment
SQL Query: Provide a valid SQL Query returning the file content and the file name in this order

A screenshot of a configuration panel with three sections: Identification, Settings, and Source. In Identification, "Action" is set to "Download." In Settings, "Multiple Files" is toggled on, with the filename "my_files.zip." In Source, an SQL query is written: "select content, filename from AOE_DEFAULT_FILES."

Showtime

Here is what it looks like, pretty nice isn't it?

New Download page process type

Patrick Wolf, from the APEX team, indicate me that I completely missed the new Download page process type for which the attributes are very similar to the Dynamic Action as you can see in the screenshot below

The main difference is that it is only supported on Before Header when the page attribute Reload on Submit is set to Only for Success. But if you set it to Always, you can also use it on page processing. Good point is that the builder validate that nicely

Conclusion

I really love these three new features. The first one allows us to write less code while maintaining the same functionality. The second and third one addresses a long-awaited demand from customers, and although we had done it before, it required much more code.

My last request would be to have two new signatures in the apex_http to download multiple files at once by providing a collection of blob/clob, filename and mime type as well as an archive name. I will probably open an idea for that!

As a final word, I would like to thank the Oracle APEX team for this release: awesome work, I love it 😍

How to Easily Download Files in Oracle APEX 24.1