Jump to content
Changes to the Jaspersoft community edition download ×

Calling Store Procedure before selecting data

Recommended Posts

By: Andy Moser - cocopelli

Calling Store Procedure before selecting data

2003-07-02 01:32



Is there a possabiliy to call a store procedure or a funtion on the database before the report is executing the select statement. I need to that for to fill up my table.

I was trying to do that in a Scriplet class in the beforeReportInit() but then I saw the select statement was already executed.


Thanks for the advise


Link to comment
Share on other sites

  • 1 year later...
  • Replies 7
  • Created
  • Last Reply

Top Posters In This Topic

I have the same requirement as described here.


Before the report query is executed, I need to invoke an Oracle PL/SQL package that preps my data and stores it in a temp table.


Based on some other posts and information from the JasperReports and iReports PDF's, I implemented a beforeReportInit() scriptlet. The good news is this works perfectly! It runs the pl/sql, loads up the temp table. Bad news is that it is executed AFTER the report query is executed. So the report query, which is going against the temp table, has no data.


Is there some other "place" to put my code that will be executed before the report query?


One alternative would be to execute the pl/sql in my own Java first, then pass the prep'd SQL Connection to JasperReports. While that would work for the deployed version, it doesn't help when trying to use iReports during the design phase.

Link to comment
Share on other sites

There is a way of working this out, and it can all be done in Oracle database (which means that it works for any reporting tool). It's a bit complicated, but it works:





1. Create a PL/SQL package in Oracle:


create package pk_Report as


--these are the fields that are the output of the



field1 VARCHAR2(10),

field2 number(10),

field3 date);




FUNCTION F_report(inParam1 varchar2, inParam2 varchar2) RETURN OUTRECSET PIPELINED;




create package body pk_Report as


--this is the procedure that we'd like to call

--before the query

procedure before_report is


--following line is necessary if you want to do some inserts, updates or deletes

pragma autonomous_transaction;




--do some DDL here....





FUNCTION F_report(inParam1 varchar2, inParam2 varchar2) RETURN OUTRECSET PIPELINED



temp outrec_type;





--this is where we call before_report procedure



--this is the query from the report:

for c in (select field1, field2, field3 from some_table where fied4=inParam1 and field5=inParam2) loop






pipe row(temp);


end loop;










2. When you call your procedure from Jasper, do it like this:


select * from table(pk_report.f_report('a,'b'))




And that's it.

Link to comment
Share on other sites

  • 1 month later...
  • 2 years later...
  • 11 months later...

 I'm sorry to dig this from the grave. 

Still calling for a solution for this. 
First populate report data to temp table then read data in multiple queries (main report/subreport with table).. So only real problem is getting the data into temp table. 
No custom java please, report's will be asked from django-application. Oracle is our used database so that package-approach would work, but sooo many years.. I hope there is a easy way to do this.

Post Edited by andde at 09/28/2011 12:44
Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...