Using Ref Cursors as a Data Source in Oracle BI Publisher

Sometimes there is a requirement to use a referential cursor in the SQL query of a BI Publisher report.
You can use ref cursors which return a single row or multiple rows.

Here is an example of a ref cursor returning a single row:
In the database create a function REF_CURSOR_CUST in the demo schema OE

create or replace function REF_CURSOR_CUST (p_customer_id IN number) return SYS_REFCURSOR as TYPE t_cursor is REF CURSOR; v_cursor t_cursor; begin open v_cursor FOR select customer_id, cust_first_name, cust_last_name, credit_limit, cust_email, gender, income_level from customers where customer_id = p_customer_id; RETURN v_cursor; end REF_CURSOR_CUST;

In the data definition of the BI Publisher report you can now use the ref cursor in your query:
select ref_cursor_cust(:cust_id) from dual

Because of a small bug this query will return no rows if you test it against the BI Publisher server 10.1.3.3.1.
Just add something to the select-list and it will work as expected:
select null, ref_cursor_cust(:cust_id) from dual

An example which returns multiple rows will work in a similar way:
In the database create a function REF_CURSOR_DEPT in the demo schema OE

create or replace function REF_CURSOR_DEPT (p_deptno IN number) return SYS_REFCURSOR as TYPE t_cursor is REF CURSOR; v_cursor t_cursor; begin open v_cursor FOR select * from employees where department_id = p_deptno; RETURN v_cursor; end REF_CURSOR_DEPT;

In the data definition of the BI Publisher report you can now use the ref cursor in your query:
select null, ref_cursor_dept(:deptno) from dual
It will return a number of rows depending from the parameter deptno which has to be selected before the query is executed.