Application Express provides a good integration of the Oracle BI Publisher, not everyone wants to invest in this pretty expensive piece of software. A good open source alternative is JasperReports. I think most of the APEX developer out there use it. So do I!

Hopefully you are familiar with the concept but here a short explanation. JasperReports is a open source Java Modul to generate different report outputs. For easier use there is a Server Application available called JasperServer. In fact this server is a Apache Tomcat bundled with JasperReports, an Administration for the report files and configuration and several other moduls. However, reports can be called through a URL. All information which is necessary to execute a report on the server will be passed through GET parameters, including the authentication data in plain text.

http://localhost:8084/jasperserver/flow.html?_flowId=viewReportFlow&reportUnit=%2Freports%2Fsamples%2FAllAccounts&output=pdf&

You can now create a Button or Link in APEX and redirect to this URL to see a report, but obviously this is not the way how passwords should be transfered….

For that reason I wrote a package in PL/SQL, which makes it possible to tunnel all requests to hide login data and to close the jasperserver port to public.

You can download my package including the APEX plugin here but first please read the tutorial.

HOW TO INSTALL THE PACKAGE

1. Just install the including two sql files: jasperserver.pkg.sql and jasperserver.pkg.body.sql

2. Give the execution rights of the sys package utl_http to your apex schema user like that(Please change the username): 

   grant execute on utl_http to scott;
   

3. For 11g only: Configure the ACL list for the utl_http packge to give your apex schema user the permission to establish a connection. (Please change the username of your apex user, host and port of your JasperServer installation)

   begin
   dbms_network_acl_admin.create_acl (
   acl         => 'utl_http.xml',
   description => 'HTTP Access',
   principal   => 'SCOTT',
   is_grant    => TRUE,
   privilege   => 'connect',
   start_date  => null,
   end_date    => null
   );dbms_network_acl_admin.add_privilege (
   acl        => 'utl_http.xml',
   principal  => 'SCOTT',
   is_grant   => TRUE,
   privilege  => 'resolve',
   start_date => null,
   end_date   => null
   );dbms_network_acl_admin.assign_acl (
   acl        => 'utl_http.xml',
   host       => 'localhost',
   lower_port => 8080,
   upper_port => 8080
   );
   commit;
   end;
   

4. The testcase explains how to set the parameter and call the report

   DECLARE
   P_REPORT_NAME VARCHAR2(200);
   P_PARAMETER JASPERSERVER.TYPE_PARAMETER;
   BEGIN
   P_REPORT_NAME := 'AllAccounts';
   P_PARAMETER('p1') := 'val1';
   P_PARAMETER('p2') := 'val2';JASPERSERVER.port := 8084;
   JASPERSERVER.reports_folder := '/reports/samples/';JASPERSERVER.CALL_REPORT(
   P_REPORT_NAME => P_REPORT_NAME,
   P_PARAMETER => P_PARAMETER
   );
   END;
   

1. You can change the following parameters for your needs within the package

   host            VARCHAR2(255);
   port            NUMBER(5,0);
   path            VARCHAR2(255);
   username        VARCHAR2(255);
   password        VARCHAR2(255);
   https           BOOLEAN;
   reports_folder  VARCHAR2(255);
   output_format   VARCHAR2(50);
   

2. Now everything is ready to be used in the APEX. There are two ways to achieve that. You can copy the testcase code in a on-demand process or in a page process which is executed “Before Header”

3. That’s it, but instead of point 4 to 6 you can use the included APEX Plugin

 

HOW TO USE THE PLUGIN

Everything is straight forward and most parameters are self explainable. Just look at the screens of the plugin settings and the process settings.

If you want to use parameters for the report, choose a page item which contains a string in this pattern: param1=val1&param2=val2

That’s it a simple and clean solution to tunnel your reports.

Cheers