How to define/call oracle db stored procedures in OBIEE?

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem:

How to define/call oracle db stored procedures in OBIEE?

Solution:




1) You cannot use oracle stored procedures in OBIEE. You need to rewrite the procedure as an oracle function instead.
The information on why and how is documented in the following bookshelf note:
Oracle Business Intelligence Server Administration Guide > Creating and Administering the Physical Layer in an Oracle BI Repository > Using Stored Procedures with an Oracle Database

http://download.oracle.com/docs/cd/E10415_01/doc/bi.1013/b31770.pdf

Here is an extract of the bookshelf note for your reference:
Using Stored Procedures with an Oracle Database
Stored Procedures within Oracle do not return result sets. Therefore they cannot be initiated from within Oracle BI. You need to rewrite the procedure as an Oracle function, use it in a select statement in the Administration Tool initialization block, and then associate it with the appropriate Oracle BI session variables in the Session Variables dialog box.
The function uses the GET_ROLES function and takes a user Id as a parameter and returns a semicolon delimited list of group names.
The following is an example of an initialization SQL string using the GET_ROLES function that is associated with the USER, GROUP, DISPLAYNAME variables:
select user_id, get_roles(user_id), first_name || ' ' || last_name
from csx_security_table
where user_id = ':USER' and password = ':PASSWORD'

2) Also If you have OBIEE 10.1.3.3 or above you can use BI built-in EVALUATE function to call user-defined functions in database.
This function is intended for scalar and analytic calculations.

Syntax: EVALUATE('DB_Function(%1)', )

Example: SELECT e.lastname,sales.revenue,EVALUATE('dense_rank() over(order by %1 )',sales.revenue) FROM sales s, employee e;

Get Oracle Certifications for all Exams
Free Online Exams.com