About the integration (Version 2.1.0)

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 5.1.0 to render the reports.

If you have any questions, problems or enhancement requests, you can report them in the forum: https://www.opal-consulting.de/forums


Requirements


How does it work?

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.

What are the parts of the integration?

The integration kit consists of the following components:

Preparation

First of all, we need to design the report. The preferred method is to use the iReport designer to create a report definition file. 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.

One roundtrip ...

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.:

begin
  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;
end;

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.

Security

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.