Calling Store Procedure before selecting data

[2002 JI Open Discussion]

By: Andy Moser - cocopelli

Calling Store Procedure before selecting data

2003-07-02 01:32

Hi

 

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

Andy

[grantj]

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.

[lucianc]

Currently there is no scriptlet event which gets fired before the report data source gets created (by executing the report query). That would be useful though, so you could post a feature request for it here.

 

What you could do in the meantime is to write a custom query executer (which extends the default SQL query executer) that would perform the required preparations before the query gets executed (and it could also do some cleanup if needed). If different reports need different preparations, you could come up with a "query preparation" interface, and each report would specify an actual implementation of that interface via a report property or parameter. The query executer would then dynamically load the report "query preparation" and execute it before running the query.

 

Regards,

Lucian

[andrija]

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

--query

TYPE OUTREC_TYPE IS RECORD (

field1 VARCHAR2(10),

field2 number(10),

field3 date);

 

TYPE OUTRECSET IS TABLE OF OUTREC_TYPE;

 

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

 

end;

 

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;

 

begin

 

--do some DDL here....

null;

 

end;

 

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

is

 

temp outrec_type;

 

begin

 

 

--this is where we call before_report procedure

before_report;

 

--this is the query from the report:

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

 

temp.field1:=c.field1;

temp.field2:=c.field2;

temp.field3:=c.field3;

 

pipe row(temp);

 

end loop;

 

end;

 

end;

 

 

 

 

 

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.

[blt9881]

Couldn't find a feature request so posted one here:

http://jasperforge.org/sf/go/artf3165?nav=1

 

 

This would be very helpful indeed.

[Aristide]

What is the database you are using (ORACLE, SQL server, ...)?

[pwpwpw]

Does anyone know if this feature has been implemented?

 

Or a way around it?

[andde]

 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