OBIEE - Table Function/Stored Procedure in Direct Database Request

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Server (OBIS|nqsserver|nqs)

1 - About

How to use a table function with the direct database request

3 - Example - How to

3.1 - 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;
/

3.2 - 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)
Advertising

4 - Support

4.1 - 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)