How to call a PL SQL function in OBIEE

Hello Everyone,

Recently we raised an S R with Oracle regarding PL Sql Functions in OBIEE. We got the following answer. This could be helpful for  you also...

In general, the only ways you can execute stored procedures from OBIEE is:

- change stored procedure into an Oracle function and then use EVALUATE function in the SQL query in Analytics.
- set scripts for its execution in the connection pool used to execute the report.

I have added the recent update from Oracle Support here for reference

Applies To:

Business Intelligence Server Enterprise Edition - Version 11.1.1.7.150120 and later
Information in this document applies to any platform.

Solution

It is possible to use the EVALUATE function in OBIEE to call the function created in the Oracle DB.

Create a function in Oracle DB and use it in an EVALUATE function as shown below:

EVAULATE(‘dbfunction_name(%1,%2)’, parameter list)

In the above expression %1 and %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.

e.g., 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.

NOTE:  It is not possible to call a stored procedure using the EVALUATE function.  EVALUATE function is used for most of the DB functions and it finds a vast usage in OBIEE.


Thanks,
Nagarjuna