Blog

Download a report from within APEX

Doxxy provides a PL/SQL API which encapsulates all processing of the report generation. You pass the necessary parameters, and the API returns a BLOB which contains the desired output (DOCX or PDF). With this BLOB you can do whatever you want: you can store it in the database or on a file system, you can print it, you can mail it, download it, …

This blogpost explains how you can implement the call to the API and the download process in Oracle Application Express.

1. Code to download your report

First you need to compose the code to generate and download your Doxxy BLOB file from your web application:

DECLARE
   l_bind_variables BL$DG4O_INTERFACE.bind_variables_t;
   l_doc BLOB;
BEGIN
----------------------------------------------------------
-- Generate report
----------------------------------------------------------
   l_bind_variables('P_ORDER_ID').number_value := 1;
   l_doc := BL$DG4O_INTERFACE.generate_document(p_document => '/My Folder/Orders Document'
                                               ,p_document_template_name => 'Orders Template'
                                               ,p_bind_variables => l_bind_variables
                                               ,p_application_key => ''
                                               ,p_user_id => ''
                                               ,p_format => 'DOCX');
----------------------------------------------------------
-- Download report
----------------------------------------------------------
   -- Initialize
   htp.flush;
   htp.init;
   -- Set up HTTP header
   owa_util.mime_header('application/octet-stream',false);
   -- Set the size so the browser knows how much to download
   htp.p('Content-length:'||dbms_lob.getlength(l_doc));
   -- The filename will be used by the browser if the users does a 'Save as'
   htp.p('Content-Disposition:attachment;filename="Orders.docx"');
   -- Close headers
   owa_util.http_header_close;
   -- Download the blob
   wpg_docload.download_file(l_doc);
   apex_application.stop_apex_engine;
END;

You see two main parts in the PL/SQL code: 1) the generation of the report with the call to the Doxxy API and 2) the specifc code for downloading the BLOB.

Tips

Generate report
More information about the BL$DG4O_INTERFACE.generate_document API can be found in the Doxxy user manual.
Use the “Show API Call” button in the “Generate Preview” screen in the Doxxy UI to generate this PL/SQL code. You can copy and paste this code into your APEX application.

generate_preview

Download report
Use apex_application.stop_apex_engine (from version APEX 4.1) when downloading your report within a Before Header process. The engine will just download your BLOB and will then stop the page immediately from rendering, so your application will not go to this download page and the user will never see it.
From version APEX version 4.2 it is recommended to use the apex_application.stop_apex_engine function!

2. APEX download button + branch

There are multiple ways to add download functionality on your web application. In this section we will explain how you can create a download button with a branch containing the PL/SQL procedure to download your BLOB file.

Create button
First create a button (for example “Download”) in the region of your choice (Page Rendering part).

create_button

Fill in the fields. For “Action” choose “Submit Page”.

submit_page_action

Create a branch
Next create a branch (Page Processing part).

create_branche

Fill in the next fields. For “Branch Type” choose “Branch to PL/SQL Procedure”.

branch_to_plsql_procedure

Enter your PL/SQL procedure.
You can also create a stored procedure in the database and enter a call to this procedure here.

plsql_procedure_to_call

Continue the wizard. Select your button in the “When Button Pressed” select list.

You are ready now!

page_overview

Run your page and press the Download-button to test your download process. Your Orders.docx report will be downloaded.

sample_application

Comments are closed, but trackbacks and pingbacks are open.

Related Posts