Steps that should be done before the steps that were described in this article:
- 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.
- 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:
- create test report
- open Query Editor
- select 'plsql' as the Query Language
- 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.
Recommended Comments
There are no comments to display.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now