Jaspersoft BI Tools

iReport,Jaspersoft Design Studio,Jaspersoft Server, Schema Workbench,Analysis,Dashboards, Self Service BI, Domains,Topics,Ad-Hoc Reports, Jaspersoft ETL, Embedding BI,HTML-5 charts, Fusion Charts,JFree Charts,Drill down reports , Drill down Dashboards,Visualize.js, Performance Tuning, D3 Charts, Jaspersoft Customization

Sunday, May 01, 2016

Simple Grid Report Example using Basic Oracle Stored Procedure in Japser Design Studio 6.1 Pro

In this post you will see how to work with a basic oracle stored procedure.

Software Setup : 
1) Oracle SQL developer  Version
2) Japsersoft Design Studio 6.1 Professional
3) Jasperserver 6.2 professional 
4) Oracle Version : Express Edition

Aim of the report : Display employee data as a grid 

1) Connecting to XE schema in database client tool ( i.e., Oracle SQL developer)

NOTE :  Default username and password while installing Oracle Expression

conn sadakar/sadakar
Syntax : conn schemaName/Password

2) Writing Stored Procedure on "emp" table ("emp" table comes with oracle 11g installation). 

create or replace procedure "REF_CURSOR_SAMPLE"
(emp_cursor  IN OUT SYS_REFCURSOR)
  open emp_cursor for
    select * from emp;
end ref_cursor_sample;

Execute Stored Procedure in Oracle SQL developer:
NOTE : Make sure set serveroutput on (i.e., run this command in SQL developer before trying to print the SP result set)



PRINT :emp_data;

3) Create a simple JRXML and in the Query editor change the language type to "plsql" and call the above stored procedure as follows 

Syntax : { call procedureName(param1,param2,$P{ORACLE_REF_CURSOR})}

 4) Create a parameter called "ORACLE_REF_CURSOR" in parameters Outline section and write its type as java.sql.ResultSet

5) In the query editor manually type the field names and provide types to them as shown in below image

NOTE : If you click on "Read fields" button you may see or stop with below error message
net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query:


Caused by: java.sql.SQLException: Invalid column type: 2000 

 6) Design the report and save then preview it. 

7) Japsersoft Design studio preview  of the report. 

Report output in Jasper server:

Configure PL/SQL for Japserserver (5.5 or later)
It can be found at http://community.jaspersoft.com/wiki/config-stored-procedures-jasperreports-server-55

After successful configuration publish the report to the server and check the output.

NOTE : Create oracle "XE" data source connection before publishing the report.  Sample connection is shown in below image. 

Download : 
Sample JRXML : click me.!

References : 
1) http://community.jaspersoft.com/wiki/ireport-calling-stored-procedures
2) http://community.jaspersoft.com/wiki/how-execute-oracle-stored-procedure-jasper-report-ireport-editor-environment
3) http://community.jaspersoft.com/wiki/calling-microsoft-sql-stored-procedures-ireport

Learn more about SP's here :
1) http://www.mkyong.com/oracle/oracle-stored-procedures-hello-world-examples/
2) https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6009.htm
3) https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm 


  1. Hi Sadhakar,

    Can I use multiple ref cursors from a procedure. The procedure that I have give me outputs from two Queries as two different refcursurs.

    Is this possible to use two refcursors in Jasper to create a report.



  2. I think you can create ref cursors names of your choice with java.sql.Resultset type and use it the way explained.
    For example. call Procedure(inputParam1, refcur1, refcur2) or something like that.
    I have not tried but just a guess.