How to execute the Oracle Stored Procedure in jasper report in iReport editor environment

Steps that should be done before the steps that were described in this article:

1. Oracle XE database should be installed. You can download and install this database from the oracle site. Installation is fairly easy on Windows, and we did not receive any errors during installation process.
2. Oracle provides convenient GUI tool to administer databases, create queries and stored procedures. This is a free tool that is called SQLDeveloper and can also be downloaded from the Oracle site.

Since the previous steps were successfully completed we can start to prepare our test environment.

The example that is described in this article is based on the sample database (HR) that is supplied with Oracle 11/XE edition.

The user under which we should work with this database (HR) is locked for security purposes.

Let's unlock this user. To do this we should connect to Oracle database as SYSTEM user

In Sqldeveloper create new connection with the name 'HR' to HR database that had already been defined and unlocked in Oracle.

Connect to HR database as HR user

Create a stored procedure emplist_proc. Beneath I quote the text of this Stored 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; 

Click Run button to create Stored Procedure.

After the Stored Procedure appeared execute it to check that it works. It should be sucsessfully executed and return the result set of rows.

At this point we have done all things in Oracle and can start iReport editor.

Let's create a connection to HR oracle database. To do this we should point the path to Oracle driver

and configure connection to HR Oracle database

Then we should:

1. create test report
2. open Query Editor
3. select 'plsql' as the Query Language
4. add the string that should execute the Oracle Stored Procedure: {call emplist_proc($P{ORACLE_REF_CURSOR})}

To automatically retrieve the fields that should be returned by the Stored Procedure click Read Fields button.

If you click Read Felds button and receive message "ERROR:SQL Problems:Invalid column type: 2000"

please create these fields manually according to their description in the HR database.

At that point we can try to execute the report and test the Oracle Stored Procedure by clicking Preview button.

As we see on last screenshot the report that was based on the Oracle Stored Procedure was successfully executed.

Thats all. Thank you for reviewing my article :)

If you are planning on deploying report which is calling stored procedure to a web application environment - please read following article:

How to execute the Oracle Stored Procedure in jasper report in Web Application environment

or

Calling Oracle Stored Procedures from JasperReports Server

It will save you trouble in stepping on common pitfalls.