Overview
This document is about how to use a oracle stored procedure in jasper report.
Technologies and Tools Used
The following technologies have been used to work with a oracle stored procedure in jasper report.
- Jasper Reports
Use Case
Let us have the requirement on how to use a oracle stored procedure in jasper report..
Steps with Screenshot
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.
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
Employee Table:
Select First_name,Last_name,email from employees where rownum<10;
Procedure:
Create or replace procedure emplist_proc(emp_cursor out sys_refcursor) Is
Begin
Open emp_cursor For
Select First_name,Last_name,email from employees where rownum<10;
End;
/
Procedure Created.
Step 1: 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 {}.
Code:
{call emplist_proc( $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.
Output:
Conclusion:
This article describes how to use an Oracle stored procedure in a Jasper report, which enables developers to use and refer to stored procedures in Jasper as PLSQL.
