### About the integration

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 Jasper Reports Integration is based on the presentation I have already blogged about. In this presentation you will find the details about the architecture and the integration.

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

- a J2EE application to call the specified report definition file, make a connection to the target database and return the generated report
  - This J2EE application (JasperReportsIntegration.war) is deployed in a standard J2EE container, I have used a Tomcat 5.5 J2EE container.
  - Also, all reports we want to use have to be copied to the J2EE server, so that they are accessible by the J2EE application. These Jasper Report definition files are usually created with the iReport designer, this is the most convenient way.
  - Finally, we need to configure different datasources in the J2EE server we want to use for connecting to different Oracle schemas.
- a PL/SQL interface which can you can use in your application (package XLIB_JASPERREPORTS)
  - This package essentially takes the parameters and constructs a URL for the J2EE application. This URL is then passed to the J2EE application via UTL_HTTP. Once the report is run, it will return the report as a BLOB. This BLOB can be displayed immediately, stored in the database or sent via Email

#### 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 Tomcat J2EE server we have already defined a native data source named default. For example this could be the HR schema in our target database.

Thus the J2EE application will use this JDBC connection from the internal 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.