How to use jasper reports
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 ProcedureSource: brianburridge.com