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

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

Software Setup : 
1) Oracle SQL developer  Version 4.0.3.16
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
USER NAME : SYS
PASSWORD : AS SYSDBA

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)
as
begin
  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)


VARIABLE emp_data REFCURSOR;

EXECUTE REF_CURSOR_SAMPLE(:emp_data);

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 
{       call REF_CURSOR_SAMPLE($P{ORACLE_REF_CURSOR})        } 

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:

{call REF_CURSOR_SAMPLE($P{ORACLE_REF_CURSOR})}

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