The main purpose is to provide a cost free alternative for high-fidelity printing / PDF generation with Oracle APEX. For doing so, I have built an integration kit to easily run Jasper Reports reports from within your Oracle APEX application.
The current release of the JasperReportsIntegration uses JasperReports 6.1.1 to render the reports.
For the integration I have chosen the Open Source reporting engine Jasper Reports (basically a java library) to run reports against an Oracle database to produce reports in various formats like PDF, RTF and XLS.
This integration approach focuses on the integration with Jasper Reports, but is not limited to it. Using a generic URL-based approach, it can easily be extended to call any other reporting engine that provides a URL based interface for running reports, for example Oracle Reports, BIRT or Crystal Reports.
First of all, we need to design the report. The preferred method is to use the newer Jaspersoft Studio to create the report definition file, but reports created with the older iReport designer will also work. We use a JDBC connection to connect to the target Oracle Schema and design the report. The report definition file is then copied into a specific directory on the J2EE server so that it can be used by the J2EE application (JasperReportsIntegration).
Next we use the PL/SQL call interface in our APEX application to call the report and pass all required parameters to it.
Once the user invokes the report generation functionality (by clicking on a link or a button), usually a PL/SQL process is invoked to call the report, e.g.:
xlib_jasperreports.show_report (p_rep_name => :p5_rep_name,
p_rep_format => :p5_rep_format,
p_data_source => :p5_data_source,
p_out_filename => :p5_out_filename,
p_rep_locale => :p5_rep_locale,
p_rep_encoding => :p5_rep_encoding,
p_additional_params => :p5_additional_params);
-- stop rendering of the current APEX page
apex_application.g_unrecoverable_error := true;
This will result in a URL like http://localhost:8080/JasperReportsIntegration/report?_repName=test&_repFormat=pdf&_dataSource=default&_outFilename=myTest.pdf&_repLocale=de_DE&_repEncoding=UTF-8&p_deptno=10
Within the J2EE server we have already defined a data source named default. For example this could be the HR schema in our target database.
The J2EE application will use a JDBC connection from the connection pool to connect to the schema HR and run the report named test.
Since the report format in our example is specified as PDF, the reporting engine will return a PDF document.
This PDF document will be returned as a BLOB and thus can be displayed directly in our application, stored in the database or sent via email.
This approach does not implement a security strategy at all. Nevertheless it is quite easy to implement security. By using a firewall we can prohibit access to the J2EE server and only allow communication between the Oracle database and the J2EE server. This way we easily implement all required authentication and authorization just within our APEX application.