How to run stored procedure before any other SQL is executed?

Hello BIRT experts, 
I am looking for a way to run an Oracle stored procedure in a report before any other SQL is executed. This is because I need to set a <a class='bbc_url' href='http://www.dba-oracle.com/t_dbms_session.htm'>context attribute value</a>, which then controls visibility of the data in the underlying database. The value passed to the stored procedure will be supplied as an integer parameter. 
 
It seems that a natural place to run the stored procedure is afterOpen on the JDBC data source, but there doesn't seem to be any methods available to do so. 
 
Similar question was asked in 2009 by someone else on <a class='bbc_url' href='http://stackoverflow.com/questions/1177390/eclipse-birt-and-oracle-need-to-set-role-before-running-report'>stackoverflow</a> but no code was posted. I'm hoping the experts on this forum will be able to help! 
 
Thanks.

Find more posts tagged with

Comments

mcremer

You could also return and do the select in your stored procedure.

Here is an example: http://enterprisesmartapps.wordpress.com/2011/02/24/oracle-stored-procedure-data-set-in-birt/ (we use quite complex stored procedures taht go all over the Databse and just returned a data set).

mhammer

Thanks for your reply, I had been thinking along the same lines of wrapping the report query SQL in a stored procedure which also makes the necessary context-setting call. This has a bit of an overhead if there are multiple data sets (context call is made on each one, even if just once would be enough) and requires database-level changes (new stored proc to be created). This is probably ok in my situation, but I am still surprised there doesn't seem to be a nice clean way of doing it through BIRT alone.

mcremer

<blockquote class='ipsBlockquote' data-author="'mhammer'" data-cid="81647" data-time="1313666877" data-date="18 August 2011 - 04:27 AM">
Thanks for your reply, I had been thinking along the same lines of wrapping the report query SQL in a stored procedure which also makes the necessary context-setting call. This has a bit of an overhead if there are multiple data sets (context call is made on each one, even if just once would be enough) and requires database-level changes (new stored proc to be created). This is probably ok in my situation, but I am still surprised there doesn't seem to be a nice clean way of doing it through BIRT alone. </blockquote>
 
Well Its because Stored Procedures are Oracle specific BIRT is set up in quite generic way. So its quite logical there is however also a method using the WITH clause witch is a bit more generic but needs a bit more of pre thinking how to do your querying.

mhammer

I wouldn't call stored procedures "Oracle specific", most databases include this concept. Anyway, the question can be viewed as "how execute particular SQL before any BIRT data sets are processed". Whether or not it is a stored procedure does not matter. I saw your blog post regarding the WITH clause, it's a neat way of passing parameters but I don't think it helps in this instance (can you call a stored procedure from the WITH clause?).