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

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