How to call Stored Procedures from JasperReports

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.

Note: 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.

I have provided all the sql used in this demo, in this file.

Setup
For this example, the table Presidents will be used. Sample data will also need to be loaded into the table. You can use the file I provided above, with all the example’s sql to create the table and load it with sample data.

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:

CREATE GLOBAL TEMPORARY TABLE “TEMP_PRESIDENTS” (
ID NUMBER(10) not null,
NAME VARCHAR(32) not null,
BIRTHDATE DATE not null,
PARTY char(1) not null
) ON COMMIT PRESERVE ROWSStep 2: Create the Stored Procedure
Next, create the stored procedure which will perform the needed data gathering. In this simple example, the query will select all rows based on the party passed (R for republican, D for democrat).

CREATE PROCEDURE “LOAD_TEMP_PRESIDENTS” (
partyParam CHAR )
as
begin
EXECUTE IMMEDIATE ‘TRUNCATE TABLE TEMP_PRESIDENTS’;
COMMIT;

INSERT INTO TEMP_PRESIDENTS
SELECT ID, NAME, BIRTHDATE, PARTY FROM PRESIDENTS WHERE PARTY = partyParam;
COMMIT;
end;Step 3: Test the Stored Procedure
Before proceeding any further, call the stored procedure and then check the temp table to be sure it behaves properly (sql below). You should get a result of all the Democrat Presidents, four of them. If not, you will need to retrace your steps.

call LOAD_TEMP_PRESIDENTS(‘D’);
select * from TEMP_PRESIDENTS;Step 4: Create the Return Type
This step creates the type that will be used to return the results from the temp table. This type should describe the result set you are expecting in the Jasper Report.

CREATE OR REPLACE TYPE “PRESIDENT_TYPE” AS OBJECT (
ID NUMBER(10),
NAME VARCHAR2(32),
BIRTHDATE DATE,
PARTY CHAR(1)
)Step 5: Create a Table of the Type
In this step we create a table of the type we created in the previous step. This “table” is what we will be selecting from in the Jasper Report. It is not a real table, but instead a type or object representing the structure of the table that we will funnel the stored procedures results through.

CREATE OR REPLACE TYPE “PRESIDENT_TYPE_TABLE” AS TABLE OF “PRESIDENT_TYPE”Step 6: Create the Stored Function
The next step is to create the stored function with the following code, to retrieve all the presidents for the party you select (R or D).

CREATE OR REPLACE FUNCTION “PRESIDENTS_FUNC” (
partyParam CHAR
)
return PRESIDENT_TYPE_TABLE pipelined
is
PRAGMA AUTONOMOUS_TRANSACTION;

TYPE ref0 is REF CURSOR;
myCursor ref0;
out_rec PRESIDENT_TYPE := PRESIDENT_TYPE(0, null, null, null);

BEGIN
LOAD_TEMP_PRESIDENTS(partyParam);

open myCursor for
select id,
name,
birthdate,
party
from TEMP_PRESIDENTS;

LOOP FETCH myCursor into
out_rec.ID,
out_rec.NAME,
out_rec.BIRTHDATE,
out_rec.PARTY;

EXIT WHEN myCursor%NOTFOUND;
PIPE ROW(out_rec);
END LOOP;
CLOSE myCursor;

RETURN;
END;Step 7: Testing and Using the Stored Function
In order to use the stored function you execute the code below:

select * from table(PRESIDENTS_FUNC(‘D’))This code can now be used within a Jasper Report, as you have turned a stored procedure into a stored function accessible with a standard select. To the Jasper Report you are merely issuing a standard query.