Redirect to another page after downloading PDF file from REST api in Oracle Apex

Question

I have an interactive report, in which a column is clicked and then a PDF file downloaded by calling a REST API. This column is a link to another page where Before page header process runs and downloads the PDF in browser. This is code for Before header process on the linked page

DECLARE
    lc_response     BLOB;
    l_blob          BLOB;
    l_url           varchar2(4000);
    lm_multipart    apex_web_service.t_multipart_parts;
    lc_json         CLOB := '[{
    "D1": 194645,
    "R1": 1,
    "R2": 9,
    "R3": 4,
    "R4": 6,
    "R5": 4,
    "R6": 5
    }]';
    lv_filename     VARCHAR2(20) := 'my_file.pdf';
    lv_base_url     VARCHAR2(100) := 'http://10.50.200.11:27005/report'; -- webservice base URL; 
    lb_blob         BLOB; -- Blob part of the multipart
    l_file_mimetype apex_application_files.mime_type%TYPE;
BEGIN

    -- Select unfilled PDF template stored as BLOB in database
    SELECT
        input_template
    INTO lb_blob
    FROM
        python_input_template
    WHERE
        sno = 1;

    apex_web_service.set_request_headers(
                                        p_name_01  => 'x-api-key',
                                        p_value_01 => 'qwerty_key',
                                        p_name_02  => 'x-api-user',
                                        p_value_02 => 'APEX'
    );

    /*Supply data to fill the pdf with*/
    apex_web_service.append_to_multipart(
                                        p_multipart    => lm_multipart,
                                        p_name         => 'data_str',
                                        p_content_type => 'application/json',
                                        p_body         => lc_json
    );
    /*Supply the template to be filled*/
    apex_web_service.append_to_multipart(
                                        p_multipart    => lm_multipart,
                                        p_name         => 'template',
                                        p_filename     => lv_filename,
                                        p_content_type => 'application/pdf',
                                        p_body_blob    => lb_blob
    );

    lc_response := apex_web_service.make_rest_request_b(
                                                     p_url         => lv_base_url,
                                                     p_http_method => 'POST',
                                                     p_body_blob   => apex_web_service.generate_request_body(lm_multipart)
                   ); 

    sys.htp.init;
    owa_util.mime_header(
                        'application/pdf',
                        false,
                        'UTF-8'
    );
    sys.htp.p('Content-Disposition: attachment; filename="'
              || 'l_file_name.pdf'
              || '"');
    sys.htp.p('Content-length: '
              || sys.dbms_lob.getlength(lc_response));

    sys.owa_util.http_header_close;
    wpg_docload.download_file(lc_response);
    apex_application.stop_apex_engine;

    l_url := APEX_UTIL.PREPARE_URL(
        p_url => 'f?p=' || :APP_ID || ':6:'||:APP_SESSION||'::NO:::',
        p_checksum_type => 'SESSION');
    apex_util.redirect_url ( p_url => l_url );
END;

Now the issue arise due to apex_application.stop_apex_engine; in above code. If I remove this line, then file isn't downloaded at all. And if I keep it, file is downloaded but no redirect happen.

P.S: I know I can redirect to same page on linked column click with some value and have the before header page process on same page and make this process conditional on this some value. that would remove the need to have redirect in PLSQL after downloading the file. But due to complexity of page I want to keep that in different page.

Any ideas on how could I download the file and then redirect to the main page where the user clicked the link column on report?

Oracle Apex 21.2 with 19c database

Answer 1

Docs:
"STOP_APEX_ENGINE
Procedure signals the Application Express engine to stop further processing and immediately exit to avoid adding additional HTML code to the HTTP buffer. This procedure raises the exception apex_application.e_stop_apex_engine internally. You must raise that exception again, if you use a WHEN OTHERS exception handler."

You could use WHEN OTHERS exception handler to deal with the apex_application.e_stop_apex_engine exception. To do so you will need to raise the same exception again which will activate WHEN OTHERS exception handler. Try rewriting your code after the apex_application.stop_apex_engine; line to something like this:

... ...
    apex_application.stop_apex_engine;  -- exception is raised
EXCEPTION
    WHEN apex_application.e_stop_apex_engine THEN
        raise; -- raise it again - this should provoke OTHERS exception handler ...
    WHEN OTHERS THEN    -- code to handle the exception
        l_url := APEX_UTIL.PREPARE_URL(
            p_url => 'f?p=' || :APP_ID || ':6:'||:APP_SESSION||'::NO:::',
            p_checksum_type => 'SESSION');
        apex_util.redirect_url ( p_url => l_url );
END;

OR within the original exception handler:

... ...
    apex_application.stop_apex_engine;    -- exception is raised
EXCEPTION
    WHEN apex_application.e_stop_apex_engine THEN  -- code to handle the exception
        l_url := APEX_UTIL.PREPARE_URL(
            p_url => 'f?p=' || :APP_ID || ':6:'||:APP_SESSION||'::NO:::',
            p_checksum_type => 'SESSION');
        apex_util.redirect_url ( p_url => l_url );
END;