How to execute the Oracle Stored Procedure in jasper report in Web Application environment

This article builds on top of the following article: How to execute the Oracle Stored Procedure in jasper report in iReport editor environment

As a new developer who is on a learning curve of using Jasper Reports in web applications, you will discover that there is very limited information about more advanced features of Jasper Reports like calling stored procedures and using ORACLE_REF_CURSOR sa result set.

And the above linked article does a great job in describing how you go about building report which calls stored procedure and uses ref cursor.

However once you take this report and deploy it to your web application - you will learn that it will not work in the web application environment.

This article is the result of hours of research and trial and error attempts to make it work. And I share it with the hope that it will save time and trouble for others going the same route.

Here are the steps you need to take to make Jasper report work in your web application:

1. Download JasperReports Library from http://sourceforge.net/projects/jasperreports/files/jasperreports/

2. Copy jasperreports-{version}.jar, iText-{version}.js1.jar, commons-digester-{version}.jar and groovy-all-{version}.jar under your web application lib folder (WEB-INF/lib).

3. Download JasperReports Extension Library from http://www.java2s.com/Code/Jar/j/Downloadjasperreportsextensions353jar.htm;

Then unzip and copy jasperreports-extensions-3.5.3.jar under your web application lib folder (WEB-INF/lib).

NOTE: You will need this extension library in order to execute plsql calls to stored procedure and without this extension your report which calls stored procedure will not work.

Then as an example of web application code to create and return PDF report will look something like this:

try {
   // Custom code to get DB Connection
   Connection conn = getDataSource().getConnection();

   // This will be output PDF stream
   ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

   // You will need to specify relative path to your Jasper report:
   URL fileUrl = getClass().getClassLoader().getResource("path to report.jasper");

   JasperReport jasperReport = (JasperReport) JRLoader.loadObject(fileUrl);

   /* This line sets correct factory to parse and process PLSQL procedure calls.
      The PlSqlQueryExecuterFactory class is located in jasperreports-extensions-3.5.3.jar */

   jasperReport.setProperty( "net.sf.jasperreports.query.executer.factory.plsql"
                            ,"com.jaspersoft.jrx.query.PlSqlQueryExecuterFactory");

   /* You may need to execute following line just once in your application.
     You can try to move it into initialization or static code block.
     Also you may notice that line below is marked as deprecated. 
     However in my case the export to pdf fails without this line being executed. */

   JRProperties.setProperty( JRQueryExecuterFactory.QUERY_EXECUTER_FACTORY_PREFIX+"plsql"
                           ,"com.jaspersoft.jrx.query.PlSqlQueryExecuterFactory");

   /* Prepare Jasper print and exporter objects in lines below */

   JasperPrint print = JasperFillManager.fillReport(jasperReport, params, conn);
   JRExporter exporter = new JRPdfExporter();
   exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, outputStream);
   exporter.setParameter(JRExporterParameter.JASPER_PRINT, print);
   exporter.exportReport();     // Generate PDF report
   return outputStream;         // Return stream result to your application code
                                // to wrap in response object.
}
catch (Exception ex) {
   log.severe("Exception loading pdf: " + ex.getMessage());
}

This should be all you need. If you find this article useful - please mark it as such.

- Eugene Luzgin (eluzgin@gmail.com)