Export SQL queries using Oracle APEX (apex_data_export) and download multiple XLSX files

Question

I'd like to create something in Oracle APEX where by selecting checkboxes, I can execute specific SQL selects and download them in xlsx format. However, I want to be able to select multiple checkboxes at once and download multiple files simultaneously. Here's the code I have, but the issue is that it only downloads the first file each time.

This is written in a process and runs when we click a button. The code roughly likes this:

DECLARE
     l_context1 apex_exec.t_context;
     l_context2 apex_exec.t_context;
     l_export1  apex_data_export.t_export;
     l_export2  apex_data_export.t_export;
BEGIN

l_context1 := apex_exec.open_query_context(
    p_location    => apex_exec.c_location_local_db,
    p_sql_query   => 'select * from emp' );

l_export1 := apex_data_export.export (
                p_context   => l_context1,
                p_format    => apex_data_export.c_format_xlsx );

apex_data_export.download( p_export => l_export1 );

apex_exec.close( l_context1 );

l_context2 := apex_exec.open_query_context(
    p_location    => apex_exec.c_location_local_db,
    p_sql_query   => 'select * from dept' );

l_export2 := apex_data_export.export (
                p_context   => l_context2,
                p_format    => apex_data_export.c_format_xlsx );

apex_data_export.download( p_export => l_export2 );

apex_exec.close( l_context2 );

EXCEPTION
WHEN others THEN
    IF l_context1 IS NOT NULL THEN
        apex_exec.close( l_context1 );
    END IF;

    IF l_context2 IS NOT NULL THEN
        apex_exec.close( l_context2 );
    END IF;

    raise;
END;

I've read that apex_data_export.download is capable of handling only one download process at a time. Is this true? Is there a solution for this?

Answer 1

The idea explained by @koen-lostrie in the comments can be implemented using the following code:

DECLARE
     l_context1 apex_exec.t_context;
     l_context2 apex_exec.t_context;
     l_export1  apex_data_export.t_export;
     l_export2  apex_data_export.t_export;
    l_zip_file blob;

    v_vcContentDisposition VARCHAR2 (25)  := 'inline';
    v_file_name VARCHAR2(255) := 'download.zip';
BEGIN

    --- create emp xlsx file then zip it
l_context1 := apex_exec.open_query_context(
    p_location    => apex_exec.c_location_local_db,
    p_sql_query   => 'select * from emp' );

l_export1 := apex_data_export.export (
                p_context   => l_context1,
                p_format    => apex_data_export.c_format_xlsx);

apex_zip.add_file (
            p_zipped_blob => l_zip_file,
            p_file_name   => 'emp.xlsx',
            p_content     => l_export1.content_blob );
    
    --- create dept xlsx file then zip it
l_context2 := apex_exec.open_query_context(
    p_location    => apex_exec.c_location_local_db,
    p_sql_query   => 'select * from dept' );

l_export2 := apex_data_export.export (
                p_context   => l_context2,
                p_format    => apex_data_export.c_format_xlsx );

apex_zip.add_file (
            p_zipped_blob => l_zip_file,
            p_file_name   => 'dept.xlsx',
            p_content     => l_export2.content_blob );

APEX_ZIP.FINISH (p_zipped_blob =>  l_zip_file);

    --- download the zip file
    OWA_UTIL.MIME_HEADER( 'application/zip', FALSE );
    HTP.p('Content-Length: ' || DBMS_LOB.GETLENGTH(l_zip_file));
    HTP.p('Content-Disposition: ' || v_vcContentDisposition ||'; filename="' || v_file_name || '"');
    OWA_UTIL.http_header_close;
    WPG_DOCLOAD.DOWNLOAD_FILE(l_zip_file);
    APEX_APPLICATION.STOP_APEX_ENGINE;

EXCEPTION
WHEN others THEN
    IF l_context1 IS NOT NULL THEN
        apex_exec.close( l_context1 );
    END IF;

    IF l_context2 IS NOT NULL THEN
        apex_exec.close( l_context2 );
    END IF;
    raise;
END;