Calling Oracle Stored Procedure in JasperReports

In my previous article I have very briefly explained how  PLSQL has been supported in jasperreports. But it is not that easy when it comes to different databases. Here I will present how we can call stored procedure from jasperreports. For this sample I have used iReport  4.1.2 designer and Oracle database, it is supported in previous versions as well, may be with minor changes.

Well to begin with we need to create a procedure. That returns cursor. Here I am using a simple procedure with sample database available in oracle (HR).

Employee table

Procedure

Now follow steps to implement stored procedure in jasper report. 

Create a new blank report.

1.  Add PLSQL executer to jasperreports.properties. 

Select Menu Tools>>options>>query Executers 

Find that below entry is added in the properties, if you are using latest version it will be already there for older version you need to add it separately. Please note that for this you need a jar jasperreports-extensions-3.5.3.jar which will be available in 4.1.2 version.

Language =plsql

Factory Class =com.jaspersoft.jrx.query.PlSqlQueryExecuterFactory

Fields Provider Class =com.jaspersoft.ireport.designer.data.fieldsproviders.SQLFieldsProvider

2.  Also go to classpath tab and make sure you added classess12/14/ojdbc.jar in classpath  

3.  Make sure that you have created oracle datasource connection available.
4.  Open  report Query , select Query Language = “plsql”  

5.  Write your plsql query as below make sure you have  procedure call withing  curly braces “{}“

{call emplist_proc($P{ORACLE_REF_CURSOR} ) }

If you have your procedure accepting more input parameters you can have them passed as parameters.

6.  If you notice that parameter “ORACLE_REF_CURSOR” is available in Available Parameter list we need to pass it as our OUT parameter for the stored procedure which will return cursor.  Also note that if you are using old versions you can create your own parameter name “cursor” with datatype as “java.sql.ResultSet”  and also make sure “use for prompt” is not selected.
7.  Read fields may not work in some cases but its ok. You can manually add them if do not work.
8.  Place the fields and you are done !. 

9.  Click on preview and you will see that your report is generated.

10. lastly, if possible before closing, click on add to support me. Thanks