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