## About the integration (Version 2.3.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 6.1.1 to render the reports.

### Requirements

- Oracle Database Release: The minimum requirement is the availability of the package UTL_HTTP. Thus the integration will work with all Oracle versions in which Oracle APEX can be installed, even Oracle XE (10g).
- Oracle APEX Release: The minimum release for the test application is APEX 4.0. Aside from the test application is no dependency on a specific Oracle APEX Release, therefore it should work with any Oracle APEX Release.
- 100MB of disk space.
- J2EE Server: I am still compatible with the fairly old servlet specification 2.5 (Java JDK 1.6), thus the J2EE application should work in most J2EE servers, it even works with Apache Tomcat 5.5.

### 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.
  - 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 or the newer Jaspersoft Studio, this is the most convenient way.
  - Finally, we need to configure the datasources in the J2EE server (or the application.properties file) to connect to our Oracle databases.
- 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 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.

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