Calling Database function in OBIEE
(7 votes, average: 4.29 out of 5)
Loading ...Hello,
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.
Comments & suggestions are most welcome.
Its really gud info. I never came across such scenario. Would appreciate if you can articulate it into a requirement. That would help a lot.
Hi Ashish
Yes sure! Some of the common Database functions like TO_CHAR and TO_DATE don’t work in OBIEE as is, but we can make use of the Evaluate function for the same and can use these functions in that. Eg:
EVALUATE(‘TO_CHAR(%1,%2)’ ,”TM_DIM”.Start_Date,’DD-MON-YY’)
In the above expression %1 and %2 accepts the values “TM_DIM”.Start_Date and ‘DD-MON-YY’ and process the request.
I hope this helps you to understand the basic logic behind using Evaluate in OBIEE. Let me know in case of any furthur queries…
This is awesome!!
To call a stored function/procedure:
EVALUATE(‘owner_name.dbfunction_name(%1,%2)’, parameter list)
owner_name is the owner’s username for example:
EVALUATE(‘scott.myfunc(%1,%2)’, parameter list)
I was able to successfully call a function created by myself in the database from OBIEE.
I hope this helps!
Fernando – BI & GRC Consultant at Quanam Group
Thanks Fernando..
I will surely give it a try…
Can we use this EVALUATE in OBIEE Answers or just in the BMM Layer of the rpd ?
Yes Jon, We can use the Evaluate function in Answers as well. Infact you can find this in functions in column f(x) in answers.
Thank you All. Good Info.
hi..
i m trying to call the function in direct database request…
i m not able to do it. i m getting the below error
[nQSError: 43093] An error occurred while processing the EXECUTE PHYSICAL statement. [nQSError: 17001] Oracle Error code: 900, message: ORA-00900: invalid SQL statement at OCI call OCIStmtExecute: EVALUATE(‘guesthouse.FUNCTION3(%1)’,3)
please help..
Thanks,
Kani
Hi Kani,
Can you please try removing the Evaluate function and call the DB function directly, as the SQL in Direct DB Request is fired directly on the database.
Leave your response!
Recent Posts
Categories
Archives
Tags
Most Commented
Most Viewed