How to integrate JasperServer and Oracle E-Business Suite

Suppose you want to use JasperServer (3.7) to generate reports containing data from the E-Business Suite. (In case you wonder, JasperServer is an open-source product that can be used to host JasperReports reports).
In many cases you will just write a (huge) select statement that extracts the data. However, sometimes the data will have to come from an Oracle API, and in that case we can do something like

select some_oracle_function(x) from dual;

However, many of Oracle's APIs require some initialization first, which is done with the familiar function fnd_global.apps_initialize.
While it is still possible to include the call to fnd_global.apps_initialize in some function that you later 'select from dual' from, there is a caveat: You are likely to run into the error

ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML

This is should not be surprising, there is nothing to stop Oracle from doing commits and the like in fnd_global.apps_initialize. They might write audit-logs, or it might be something completely different. We do not know.

So we need to be able to issue a PL/SQL Statement before the select in JasperServer.

Luckily this is possible by using the language 'plsql' in queryString:

<querystring language="plsql">...

in the jrxml file.

This enables us to write stored procedures that can do all kinds of fancy PL/SQL-Stuff and then return a cursor with the desired query results.

In order to be able to do this, some extra configuration is needed.
First, you need to add the line

net.sf.jasperreports.query.executer.factory.plsql=com.jaspersoft.jrx.query.PlSqlQueryExecuterFactory


to the file
(war-file-for-jasperserver)/WEB-INF/classes/jasperreports.properties

You also have to add the string 'plsql' below 'sql' in the files (war-file-for-jasperserver)/WEB-INF/flows/queryBeans.xml
and (war-file-for-jasperserver)/WEB-INF/applicationContext.xml

Now you can write a PL/SQL-Procedure like the following:

procedure get_data_from_ebs(p_cursor out ref_cur, p_some_parameter in number) is
begin
  fnd_global.apps_initialize(-1,-1,-1);
  open p_cursor for
    select * from some_table where p_some_parameter=xyz;
end;

You have to define 'type ref_cur is ref cursor;' in your package spec.

Now you can use this procedure in the jrxml like so:

<querystring language="plsql">
<![CDATA[{call get_data_from_ebs($P{cursor}, $P{some_parameter})}]]>
</querystring>

I also had to add

<parameter name="DATABASE_TIMEZONE" class="java.lang.String" isforprompting="false">
<defaultvalueexpression><![CDATA["Europe/Berlin"]]></defaultvalueexpression>
</parameter>

in that file.

A nice side-effect is that the messy SQL is kept out of the jrxml.