OBIEE - Table Function/Stored Procedure in Direct Database Request

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.

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)

Powered by ComboStrap