This article explains how to set up JasperReports Server to run reports that use Oracle stored procedures for their queries.

Calling Oracle Stored Procedures from JasperReports Server

You will use both iReport and JasperReports Server. Before proceding, gather the connection information for your Oracle database. You will need administrator access to the JasperServer file system in order to deploy the plsql query executer.

Create and Run the Report in iReport

Read this article first to learn how to create and run reports in iReport that use stored procedures for their queries.

Define the Oracle Datasource in JasperReports Server

You need to create a JDBC or JNDI data source that points to the Oracle database you want to connect to. If you need help defining the data source, this wiki link on JasperForge provides detailed instructions: DataSources.

Deploy the plsql Query Executer in JasperReports Server

The plsql query executer was bundled with iReport 3.6. As of JasperReports Server 3.7, it has not yet been integrated into the server. These instructions show how to copy the query executer from iReport to JasperReports Server and configure it.

1. Shutdown JasperReports Server.
2. Copy ireportmodulesextjasperreports-extensions-3_5_3.zip to jasperserver-proWEB-INFlib.
3. Use a text editor to make the following addition to jasperserver-proWEB-INFclassesjasperreports.properties. Save the file and restart JasperServer.

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

Deploy Your Report to JasperReports Server from iReport

You will deploy your reports to JasperReports Server using iReport.

1. In iReport, display your saved report in the edit/design window.
2. Open the Repository Navigator panel within iReport. Navigate to a folder where you want to deploy your report.
3. Right-click and choose Add > Report Unit to launch the ReportUnit Wizard.
4. Enter a name and label for your report as you want it to display to users in JasperReports Server.
5. In the Main JRXML page click Get source from current opened report.
6. In the Locate Data Source page click From the repository and select the datasource you defined above. Click Finish.

You will be prompted to open the JRXML from the server. Click Yes if you intend to make further changes to the report.If you intend to close the report, it doesn't matter what you select.

Note: We provide instructions for deploying the report using iReport rather than JasperReports Server. When the JasperServer Add Resource > Report Wizard identifies that the report uses a custom query executer, it filters out JDBC and JNDI connections from the list of available DataSources. The plsql query executer, however, needs a JDBC or JNDI connection. Fortunately, the iReport ReportUnit Wizard allows you access to all connection types.

Test Run the Report from iReport and JasperReports Server

The report should now be in the repository. You can run it from the server using either the iReport Repository Browser panel or through the JasperReports Server web application.
To run the report remotely from the server using iReport:

1. Right-click the report name in the Repository Navigator.
2. Click Run Report Unit.

To run the report using the JasperReports Server web application:

1. Log in to JasperReports Server.
2. Locate the report (use search or browse the repository).
3. Click the report name.

Tip: if the report returns no pages when run from the server and the server log reports "the supplied java.sql.Connection object is null", then the datasource connection is not properly defined for the report unit.