Calling a Stored Procedure from Jasper Reports

Calling Oracle Stored Procedure in JasperReports

We can execute a Stored Procedure in Jasper as PLSQL is supported in Jasper.

Prerequisites 

•  The procedure should have an out parameter of Cursor type.
• Any inputs to procedure should be sent as in parameters.
• We need to define field names in iReport Designer manually that exactly match the field names that are returned by cursor

Step1:  Create a blank page in iReport Designer.

 

Step2:  We need to add PLSQL Executor in order to execute PLSQL blocks.  In Latest versions of ireport Designer, it will be already added. But if you are using earlier version, you need to add the following properties.

Go to Tools>>Options>>Query Executers 

Language =plsql

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

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

Make sure that you add jasperreports-extensions-3.5.3.jar  and ojdbc14.jar files in classpath

Step 3:  Open  report Query  and select Query Language = “plsql”. If you observe the available parameters on the right hand side, you will find ORACLE_REF_CURSOR parameter which is going to be the out parameter for your procedure. Now write your PLSQL code inside curly braces {}.

{call PKG_REPORTS_API.get_shop_details_report ($P{memberNumber},$P{orderNumber}, $P{ORACLE_REF_CURSOR})}

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.

Step 4:  Now use the fields in your report and execute the report to see the results.

Results Screen