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
