How to call Stored Procedures from JasperReports June 4, 2006
Jasper Reports is unable to call Oracle stored procedures directly, because procedures do not return standard result sets. As a solution, in Oracle, you can use a stored function to retrieve the results of a stored procedure. There are a few more steps to do this than if you were able to use a stored procedure, but it currently is the only option, if the query you need to do can’t be done with a standard SQL query.
In order to to use stored functions to retrieve the result set of a stored procedure, you will need to use a temp table to hold the results, and then return the results using types and tables of types.
In this example, I have kept the function very limited. This particular query would not need to be done with a stored procedure and function, as a standard select query would be best, but is only used to demonstrated how to do it, should the need arise.
Now that there is a base table and data to work with, creation of the objects needed for the stored function can begin.
Step 1: Create a Temp Table
First, create a temp table to temporarily hold the results from the stored procedure, so the Jasper Report can query it via the stored function, with a standard select query. To create the temp table, use this sql:
Step 2: Create the Stored Procedure
Step 3: Test the Stored Procedure