how to call a Oracle store proc from iReport?

[dprogrammer]

Hi Guys, I have a simple question of the day. I need to call an Oracle store proc that takes date as parameter. I did try following sentexes but none of them is working:

execute procedure my_proc('2008-01-01')

exec my_proc ('2008-01-01')

call my_proc ('2008-01-01')

All of above are returning following error:

"Caused by: java.sql.SQLException: [Oracle][ODBC]Syntax error or access violation. "

Thanks in adavnce for your replies.

[fariszuriekat]

First, Oracle store procedure do not return data, use functions to get data to your report, some people still believe that store procedure are needed, for me personally , I think it is too much cost, in terms of time, maintenance and production , replace your store procedures with functions. but as first step you need to understand the concept of getting the data to the report.

I had the same exact problem before, read below what I learnt from Brian Burridge:http://www.brianburridge.com/2006/06/04/how-to-call-stored-procedures-from-jasper-reports/

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.

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 ROWS

Step 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 )asbegin     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 pipelinedisPRAGMA AUTONOMOUS_TRANSACTION;TYPE ref0 is REF CURSOR;myCursor ref0;out_rec PRESIDENT_TYPE := PRESIDENT_TYPE(0, null, null, null);BEGINLOAD_TEMP_PRESIDENTS(partyParam);open myCursor forselect id,name,birthdate,partyfrom TEMP_PRESIDENTS;LOOP FETCH myCursor intoout_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’))REPLACE IT WITH select * from table(cast ( PRESIDENTS_FUNC(‘D’)  as PRESIDENT_TYPE_TABLE)) 

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.

 

Post Edited by Faris Zuriekat at 12/12/08 18:58

[bklawans]

There is also a project here on JasperForge that allows you to run an Oracle stored procedure directly inside JasperReports.  Its a bit old and doesn't include iReport support, but I've started work on a new version that will support iReport 3.1, once I figure out how to get plugins working in the the NetBeans version of iReport.

 

-Barry Klawans

[dprogrammer]

Faris, thanks for your detailed solution. Atleast, I know that it is not possible to call store procs with out of the box iReport. I did search for that project which allows calling store procs but I could not find it on sourceforge for download.

 

Thanks

[mdahlman]

This is the project Barry was referring to: OracleStoredProcedures

 

Regards,
Matt

[shellybelly999]

I'm a newbie here on this forum. I found the project page for the custom JRQueryExecutor but I don't see how to download it. Is it available for download?