OBIEE - Table Function/Stored Procedure in Direct Database Request

Bi Server Architecture With Client

About

How to use a table function with the direct database request

Example - How to

In the database

CREATE OR REPLACE TYPE my_row AS OBJECT (my_num NUMBER);

CREATE OR REPLACE TYPE my_tab AS TABLE OF my_row;

CREATE OR REPLACE FUNCTION my_table_function RETURN my_tab
PIPELINED IS
BEGIN
PIPE ROW(my_row(1.23));
END;
/

In Direct Database Request

Then in the direct database request add this statement

SELECT CAST(my_num AS double precision) AS my_num3 FROM TABLE(my_table_function)

Support

Oracle number data type as an integer

When you use a table function, you may have the surprise to see that BI Presentation service see a Oracle number data type as an integer.

Obiee Direct Db Number Piplined

The solution :
Cast the number as double precision in the Sql as this :

SELECT cast(my_num as double precision) as my_num3 FROM table(my_table_function)





Discover More
Obiee Database Request Subject Area
OBIEE 10G/11G - Direct Database Request

The Direct Database Request permit you to perform SQL statement directly to a datasource. The Oracle BI Server sends unprocessed, user-entered, physical SQL directly to an underlying database. The returned...



Share this page:
Follow us:
Task Runner