Day before we came across a new requirement in which we had to call a function created in Oracle DB to OBIEE. Thought of sharing the same with you.
It is very simple if you know the trick. We made use of the EVALUATE function in OBIEE to call the function created in the Oracle DB.
Create a function in Oracle DB and use it in EVALUATE function as shown below:
EVAULATE(‘dbfunction_name(%1,%2)’, parameter list)
In the above expression %1& %2 are number of parameters to be passed to the DB function. Parameters can be more than or less than 2 depending on the requirement. These can also be constant values.
eg: EVALUATE(‘func1(%1)’,'TRADE_ID’)
So, the above expression will pass the TRADE_ID parameter to the DB function(func1) and hence give the result based on the output of the DB function.
One important thing to remember is we can’t call a stored procedure using the EVALUATE function.
We have also tried calling a stored proc using EVALUATE function but it didn’t work for us. If anybody has implemented the same kindly share the way to implement the same.
We can make use of this EVALUATE function for most of the DB functions and it finds a vast usage in OBIEE.
I hope this post will be helpful to you.
Posts
