OBIEE - Table Function/Stored Procedure in Direct Database Request

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)

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)


Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap