Using PL/SQL procedure to generate workbooks/report in Discoverer

Suppose you have a procedure that does a calculation and send the table as an out parameter.

create or replace type emp_type as object

(

EMPNO NUMBER(4),

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4),

HIREDATE DATE,

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2)

)

/

create or replace type emp_nested_table as table of emp_type;

/

CREATE OR REPLACE PROCEDURE ret_type_out (ret_table OUT emp_nested_table)

AS

BEGIN

SELECT CAST (MULTISET (SELECT *

FROM emp) AS emp_nested_table)

INTO ret_table

FROM DUAL;

END;

/

Since procedures are not supported in Discoverer, so, we create a function that executes the above procedure and returns nested table as below: -

CREATE OR REPLACE FUNCTION fun_out_param

RETURN emp_nested_table

AS

ret_table emp_nested_table;

BEGIN

ret_type_out (ret_table);

RETURN ret_table;

END;

Create a custom folder in discoverer admin using the following sql query

select * from table(fun_out_param)

Finally, create a new workbook using the above custom folder.