Calling Oracle Stored Procedure from OBIEE 11g

souvik88_570

Hi

I need to execute below procedure before the report query gets executed to display data -

EXEC APPS.MO_GLOBAL.SET_POLICY_CONTEXT('S',1103);

How can i do that from report level? can i use EVALUATE function?

[Deleted User]

2969455 wrote:HiI need to execute below procedure before the report query gets executed to display data -EXEC APPS.MO_GLOBAL.SET_POLICY_CONTEXT('S',1103);How can i do that from report level? can i use EVALUATE function? 

Depends - what's the result of the stored procedure? If it's just a value then yes you can use EVALUATE.

If you want to test what it yields in OBI you can just create a Direct Database Request with your SQL query. If it is somehting that needs to be integrated into your model then the story's different of course.

souvik88_570

Hi Christian

Thank you for your reply.

This procedure does not return any value. But we need to execute this first thing whenever a new database session is opened in order to see data from some Oracle Apps tables. As my OBIEE report queries those tables, I need to execute this procedure from OBIEE as well before the execution of report query.

[Deleted User]

Then you need to use the RPD Connection Script settings for the connection pool which is used to access your data:

souvik88_570

Thanks again for the reply.

But if I use RPD Connection Script then I think this query will be executed for all the reports which uses this connection pool. This requirement is for only one report. Can I use the "Prefix" option in "Advanced" tab?

[Deleted User]

Yes. Same thing as the one in the RPD but request-based.

souvik88_570

Ok. Can you please help me to write the correct script that I can run from 'Prefix'? I am trying the one below and it is not working  -

EVALUATE('APPS.MO_GLOBAL.SET_POLICY_CONTEXT(%1,%2)','S',1103)

[Deleted User]

EVALUATE was for columnar usage if you need the result embedded in a data set. That's why I asked for what the return of the stored proc is.

Haven't gotten the precise syntax lieing around (somewhere in my notes) but sht along the lines of EXECUTE PHYSICAL  CONNECTION POOL "Fully Qualified"."Connection Pool Name" [yourstatementgoeshere]

souvik88_570

I tried below script , but did not work -

EXECUTE PHYSICAL CONNECTION POOL "Oracle EBS OLTP Connection Pool"

EXECUTE APPS.MO_GLOBAL.SET_POLICY_CONTEXT(''S'',1103)

Thomas Dodds

The following is from BICS, but a quick look leads me to believe something similar can be done in Answers ...

http://www.ateam-oracle.com/executing-a-stored-procedure-from-oracle-business-intelligence-cloud-service-bics/

[Deleted User]

Thanks for adding that @Thomas Dodds !

Actually gave me another idea. Just add a filter with "Based on another analysis" main one and put an a single column with the EVALUATE in the analysis called by the filter. Runs first. Executes the package. Main analysis runs. Bob's your uncle