JasperReports in Apex

A lot of people complain that Oracle integrated the very expensive BI Publisher in Apex. Of course there are alternatives, like the open source JasperReports. But how do you use this?

Jasper is completely new to me, so I started with a simple example. What you need is:

• Oracle Apex (of course)
• JasperServer
• iReport

JasperServer and iReport can de downloaded from the Jaspersoft website.

Basically, iReport is a client/server graphical tool to design a report, and JasperServer runs the report in a web environment.

I created the simplest report I could think of in iReport using the Report Wizard:

select *
from emp
where deptno = $P{deptNo}

Publish this report in JasperServer. As a Jasper newbie I was surprised that in JasperServer you have to define the report parameter (deptNo) again as a control. This is not taken from the report definition. That took me some time to figure out, but once that was done, integration with Apex was easy!

Now back to Apex. Here I created another simple report based on:

select
"EMPNO",
"ENAME",
"DEPTNO",
"DEPTNO" "DEPTNO2"
from "EMP"

The two deptno columns are used to link to the Jasper report. I will explain the difference between those two.

For the first deptno column I created a simple link to JasperServer. Edit the report attribute so that it will become a link to a URL:




The URL is:

http://server:port/jasperserver/flow.html?_flowId=viewReportFlow&reportUnit=/reports/Apex/deptemp&output=pdf&deptNo=#DEPTNO#&j_username=un&j_password=pw


Now, as you can see this is not a very secure option. The username and password are exposed in the URL. Another option is to call the report from the database using the utl_http package. This is the procedure:

CREATE OR REPLACE procedure runJasperReport(i_deptno   in varchar2)
is
vReportURL       varchar2(255);
vBlobRef         blob;
vRequest         Utl_Http.req;
vResponse        Utl_Http.resp;
vData            raw(32767);
begin
----------------------------------------------------------------------------
-- build URL to call the report
----------------------------------------------------------------------------
vReportURL := 'http://host:port/jasperserver/flow.html?_flowId=viewReportFlow'||
     '&reportUnit=/reports/Apex/deptemp'||
     '&output=pdf'||
     '&j_username=un&j_password=pw'||
     '&deptNo='||i_deptno;
----------------------------------------------------------------------------
-- get the blob reference
----------------------------------------------------------------------------
insert into demo_pdf (pdf_report)
values( empty_blob() )
returning pdf_report into vBlobRef;
----------------------------------------------------------------------------
-- Get the pdf file from JasperServer by simulating a report call from the browser
----------------------------------------------------------------------------
vRequest := Utl_Http.begin_request(vReportUrl);
Utl_Http.set_header(vRequest, 'User-Agent', 'Mozilla/4.0');
vResponse := Utl_Http.get_response(vRequest);
loop
begin
--------------------------------------------------------------------
-- read the next chunk of binary data
--------------------------------------------------------------------
Utl_Http.read_raw(vResponse, vData);
--------------------------------------------------------------------
-- append it to our blob for the pdf file
--------------------------------------------------------------------
Dbms_Lob.writeAppend
 ( lob_loc => vBlobRef
 , amount  => Utl_Raw.length(vData)
 , buffer  => vData
 );
exception when utl_http.end_of_body then
exit; -- exit loop
end;
end loop;
Utl_Http.end_response(vResponse);

owa_util.mime_header('application/pdf',false);
htp.p('Content-length: ' || dbms_lob.getlength(vBlobRef));
owa_util.http_header_close;
wpg_docload.download_file(vBlobRef);

end runJasperReport;
/

To call this procedure you create a very simple On Demand Application Process:

runJasperReport(:deptno);

where :deptno is an Application Item.

Similar to the link in the first case, the URL is now:

f?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.:APPLICATION_PROCESS=runrep:::deptno:#DEPTNO2#

In a real application you will add extra parameters to the stored procedure, like logged in user. Make it re-usable by adding the report name and parameters as input parameters.

Added alternative

There is a third option to call a report. I think this is even better, since you have more authorisation control. On Demand Application Processes have the disadvantage that they do not have an authorisation control mechanism.

So, instead of calling an application process, you can call a page that has the same stored procedure as source.

Just create an empty page that calls the procedure in the On Load - Before Header process point.

In the report itself, change the link to call the page instead of the application process.