Extra Pages

▼

Thursday, February 7, 2008

OBIEE Using ORACLE stored procedures part 2

In the connection pool you are able to define connection scripts in 4 events:

  • "Execute on connect" -> Very usefull point for security logging events
  • "Execute before query" -> Ideal to fill a global temp table or to do some execution logging
  • "Execute after query" -> To close you execution loging
  • "Execute on dissconnect -> close your security log

Sadly in the OBIEE documentation (10.1.3.x ) there is no info about these options... I did some research (mostly trail and error) on how to incoperate variables in these scripts. The syntax for this is different then in the rest of OBIEE.

All parameters have to be enclosed in single quotes:

  • ':USER', ':PASSWORD'
  • 'VALUEOF(REPOSITORY_VARIABLE)'
  • 'VALUEOF(NQ_SESSION.SESSION_VARIABLE)'

If you want to pass something from the dashboard put in a session variable.

The execution string is build like

BEGIN SCHEMA.PACKAGE_NAME.PROCEDURE_NAME('PARAMETER'); END;

Don't forget the semicolons ';'

Till Next Time

at

5 comments:

  1. UnknownFebruary 20, 2008 at 5:46 AM

    Hi John,

    I found very few results on OBIEE stored procedures usage on google...already done some tests but am not heading anywhere..can you elaborate more on how to use a stored procedure with a simple example? will appreciate it.. thanks.

    Prash

    ReplyDelete
  2. John MinkjanFebruary 20, 2008 at 8:42 AM

    Hi Prash,

    I will try to make an other example this weekend

    ReplyDelete
  3. UnknownFebruary 27, 2008 at 1:01 AM

    Hi John,

    can I pass a Value from a Write Back report Into a Variable and have a Stored Procedure / function do some data validation?

    Thnx,Prash

    ReplyDelete
  4. AnonymousFebruary 10, 2010 at 9:14 AM

    Thanks really helpful

    ReplyDelete
  5. Mun' world~March 16, 2010 at 10:29 AM

    Can I add a dynamic variable to the report title? eg. Invoice from 3-Mar-2010 to 10-Mar-2010. When I download the report, the saved file name will have the date time also. Any way I can do that?

    ReplyDelete

Note: Only a member of this blog may post a comment.