OBIEE Using ORACLE stored procedures part 3

If you are using the Multi User Development (MUD) be aware that the connectionscripts aren't updated when you check in.
You have to edit them manualy in the main repository.

Till Next Time

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

OBIEE using oracle stored procedure to fill report

Oracle stored procedures by default don't return recordsets, but sometimes a query is to slow or complex. Especially if it contains multiple outerjoins or complex statistical calculations. (yes, if know how to handle table types there is an other workaround see: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:666224436920  )

In this demo I will show you a workaround. First you need to tables, one for the parameters and one for the resultset.

CREATE TABLE SH.DEMOPAR
( ID NUMBER, SDATE DATE DEFAULT trunc(sysdate
- 183), EDATE DATE DEFAULT
trunc(sysdate), MACHINENUMBER VARCHAR2(30 BYTE) )
;

CREATE
UNIQUE INDEX SH.DEMOPAR_PK ON SH.DEMOPAR (ID) ;
CREATE
TABLE SH.DEMODATE
(ID NUMBER, MDATE DATE, VALUE NUMBER, INTERPOL NUMBER, C1
NUMBER, C2 NUMBER ) ;
CREATE UNIQUE INDEX SH.DEMODATE_PK ON SH.DEMODATE
(ID, MDATE) ;

For the parameter table you need to construct an XML file for the write-back functionality:

-<-webmessagetables sawm="com.siebel.analytics.web/message/v1">
-<-webmessagetable lang="en-us" table="Messages" system="WriteBack">
-<-webmessage name="demosavepar">
-<-XML>
-<-writeBack connectionPool="CP_dateDemo">
-<-insert>INSERT
INTO DEMOPAR VALUES('@{c0}','@{c1}','@{c2}','@{c3}')
-<-update>UPDATE DEMOPAR SET SDATE = '@{c1}',EDATE =
'@{c2}',MACHINENUMBER = '@{c3}' WHERE ID= '@{c0}'
-<-/writeBack>
-<-/XML>
-<-/webmessage>
-<-/webmessagetable>
-<-/webmessagetables>

Alter the "-<-" in "<" and place the file in ?:\{OracleBI}\web\msgdb\customMessages. Next restart the presentation server. Add both tables to two separate connection pools in your repository! Be sure the cache is off.

Make one report for the parameter table and add the write-back functionality.
Next go to the connection pool for your datatable and open the "Execute before query" tab:


Here you can put your script to execute the stored procedure:
Now you can put both reports on a dashboard!
Till next time