Skip to Main Content

Breadcrumb

Overview

The FOS - Download File(s) dynamic action plug-in enables the downloading of one or multiple database-stored BLOBs or CLOBs. You don't have to worry about setting HTTP headers, converting CLOBs to BLOBs, or zipping the files. It's all done for you. Just specify which files to download via a SQL query, or a more dynamic PL/SQL code block. Multiple files are zipped automatically, but a single file can optionally be zipped as well.

The benefits of downloading a file using a dynamic action include:

  1. You have the flexibility of using SQL to decide what file(s) to download including filtering based on page item values
  2. You can have further actions defined after the download action for improved workflow
  3. You can wait for the result of the file to download before continuing your next action
  4. You do NOT issue a page submit or redirect, which means you can handle errors better i.e. you won't be redirected to an error page

Note: in "some cases" you may need to change your security setting "Embed in Frames" and set it to either "Allow from same origin" or "Allow" for this action to work correctly in preview mode. This is because the file will be downloaded in a hidden iFrame on the page. If your setting is set to "Deny" you may see a message in the console like: "Load denied by X-Frame-Options". We evaluated a number of techniques to download files and found that this technique has the best performance with larger files.

Examples

Based on a SQL Query

In most cases the file(s) will come from a SQL query, which should look like this:


select file_name    as file_name
     , mime_type    as file_mime_type
     , blob_content as file_content_blob
  from some_table

If there is only one file in the result set, by default it will be downloaded as is. You can however enforce zipping also on single files. Take the following example with the 'Always Zip' option turned off and on.


select 'sample_image.png' as file_name
     , mime_type          as file_mime_type
     , file_content       as file_content_blob
  from apex_application_static_files
 where application_id = :APP_ID
   and file_name = 'images/avatar2.png'

Multiple files will always be zipped.


select file_name    as file_name
     , mime_type    as file_mime_type
     , file_content as file_content_blob
  from apex_application_static_files
 where application_id = :APP_ID

BLOBs *and* CLOBs

Sometimes you have files stored as VARCHAR2 or CLOB, and not as binary data. This plug-in can handle both. Instead of providing a file_content_blob column, provide a file_content_clob column. In such cases you can simply define the mime_type as text/plain.


select 'test.txt'     as file_name
     , 'text/plain'   as file_mime_type
     , 'Hello World!' as file_content_clob
  from dual

You can also combine BLOBs and CLOBs! Simply provide both columns, while only populating one of them. The plug-in will always choose the non-empty column.


select 'sample_image.png' as file_name
     , mime_type          as file_mime_type
     , file_content       as file_content_blob
     , null               as file_content_clob
  from apex_application_static_files
 where application_id = :APP_ID
   and file_name = 'images/avatar2.png'

 union all

select 'test.txt'
     , 'text/plain'
     , null
     , 'Hello World!'
  from dual

Based on PL/SQL Code

The files can also be compiled procedural by adding them to a collection in a PL/SQL code block.

Simply add all files to the FOS_DOWNLOAD_FILES collection. This special collection will be created and removed accordingly by the plug-in. Just make sure to provide parameters p_c001 for the file name, p_c002 for the mime type, and p_blob001 or p_clob001 for the content.


apex_collection.add_member
    ( p_collection_name => 'FOS_DOWNLOAD_FILES'
    , p_c001            => 'README.md'
    , p_c002            => 'text/plain'
    , p_clob001         => 'This zip contains *all* application files!'
    );

for f in (
    select *
      from apex_application_static_files
     where application_id = :APP_ID
) loop
    apex_collection.add_member
        ( p_collection_name => 'FOS_DOWNLOAD_FILES'
        , p_c001            => f.file_name
        , p_c002            => f.mime_type
        , p_blob001         => f.file_content
        );
end loop;

-- pro tip: you can override the zip file name by assigning it to the apex_application.g_x01 global variable
apex_application.g_x01 := 'all_files.zip';

Creating Subdirectories

To create subdirectories in your zip structure, prepend them to the file name as follows:


apex_collection.add_member
    ( p_collection_name => 'FOS_DOWNLOAD_FILES'
    , p_c001            => 'file1.txt'
    , p_c002            => 'text/plain'
    , p_clob001         => 'I''m in the root.'
    );

apex_collection.add_member
    ( p_collection_name => 'FOS_DOWNLOAD_FILES'
    , p_c001            => 'hello/world/file2.txt'
    , p_c002            => 'text/plain'
    , p_clob001         => 'I''m two levels down.'
    );

apex_collection.add_member
    ( p_collection_name => 'FOS_DOWNLOAD_FILES'
    , p_c001            => '1/2/3/4/5/6/7/8/9/file3.txt'
    , p_c002            => 'text/plain'
    , p_clob001         => 'Hello? Anybody there?'
    );

Advanced: Download Application Components

This plug-in works great in combination with the apex_export API. In the following example we create on export of all the plug-ins included in this application.


declare
    l_files      apex_t_export_files;
    l_components apex_t_varchar2;
    
    l_set_env varchar2(32767);
    l_end_env varchar2(32767);
    
    CRLF constant varchar2(4) := chr(13) || chr(10);
begin
    select 'PLUGIN:' || id
      bulk collect into l_components
      from apex_appl_export_comps
     where application_id = :APP_ID
       and type_name = 'PLUGIN'
       and name like 'FOS - %';

    -- export application
    -- we can speed things up by only exporting the needed components
    l_files := apex_export.get_application
        ( p_application_id => :APP_ID
        , p_split          => true
        , p_components     => l_components
        );
    
    -- individual component files do not contain the necessary start and end environment scripts
    -- so in this case we must fetch, prepend and append them ourselves
    select contents
      into l_set_env
      from table(l_files)
     where name like '%set_environment.sql';

    select contents
      into l_end_env
      from table(l_files)
     where name like '%end_environment.sql';
    
    for idx in 1 .. l_files.count
    loop
        if l_files(idx).name like '%/plugins/%'
        then
            -- file names are in the form application/shared_components/plugins/dynamic_action/...
            -- performing a substr to only use the actual plug-in name. i.e everything after the last slash
            apex_collection.add_member
                ( p_collection_name => 'FOS_DOWNLOAD_FILES'
                , p_c001            => substr(l_files(idx).name, instr(l_files(idx).name, '/', -1) + 1)
                , p_c002            => 'text/plain'
                , p_clob001         => l_set_env || CRLF || l_files(idx).contents || CRLF || l_end_env
                );
        end if;
    end loop;
end;

Advanced: Executing PL/SQL to Track Download Counts

When using a SQL Query to return your file(s) to download you have the option to execute a PL/SQL code block just prior to the download starting. This is to allow you to perform actions like tracking the download count. Your changes will be automatically committed.


update my_table set download_count = nvl(download_count, 0) + 1
where  file_name = 'somefile.zip';

Detecting Download Errors

When an exception is raised during download, an error notification will be shown and a plug-in event will be fired. You have the option of suppressing this error notification and performing your own custom error handling. When we trap the error we also ensure that we run it through the page/application error handling routine (if you have defined one).

You can access the error message in dynamic action client side conditions or in execute javascript code actions using the following syntax:

this.data.error

Preview Mode

You can choose to download the file to the filesystem or preview the file using an "inline" content-disposition. There are some restrictions to using preview mode i.e. we only support the following file types:

  • Text Files
  • Image Files
  • PDF Files

Note: if you preview a file type in a new window that is not supported the behaviour is based on the browser which in most cases results in a file download and a blank preview window.

You can customize/override the dialog settings using the "Javascript Initialization Code" attribute e.g.


function(options) {
   // you can change any of the settings defined here: https://api.jqueryui.com/dialog/
   options.previewOptions = options.previewOptions || {};
   options.previewOptions.modal = false;
   return options;
}

Interactive Grid: Choosing Files to Download

You can use an interactive grid with it's multi-selection capability to choose which files you wish to download.

Download Files

Design

You can preview the plug-in setup as you would see it in page designer. You can either do this by clicking this button in the top right corner of each example, or you can see all the examples together in the region below.

Looking at the examples you'll see just how easy the plug-in is to use. Don't worry about changing any values as they aren't saved. We actually encourage you to change them, so you can see the behaviour of the attributes and their help text.

FAQ

  • How can you download a file via AJAX?

    There are several techniques you can use to download a file via AJAX. One option is to return the file in a base64 encoded string in a JSON response, however this has some processing and extra memory overheads. An alternative approach that we have used in this plug-in is that you can submit a HTML form and set the result target to an iFrame, meaning the file will be downloaded in the iframe. Using this technique means that we can support larger files.

    The iFrame method is technically not an actual AJAX call, it behaves almost identical as we submit a form POST in the background and the file downloads in the iFrame without affecting/blocking the user interacting with the page. You can find some more detailed information here about downloading files via AJAX.

    There are some additional challenges with this iFrame technique, like detecting when the file has been downloaded. If you're interested in understanding the technical details about how we solved this problem, you can find more information here.

  • My file(s) won't download, why?

    As we download files using a hidden iframe you may have to update your security settings attribute "Embed in Frames" to "Allow from same origin" in order for it be allowed. When set to "Deny" the file can be blocked from being downloaded. If it is, you will see an error message in the developer tools console similar to the following:

    "Load denied by X-Frame-Options"