Far too many enquiries recently about how to hook BIP up with a REF CURSOR.So here it is, thanks to Juergen in Germany for the sample.
You need to create a function in the database that will return the ref cursor when called, of course you can pass parameters to the function just like any other function. Here's a simple example that selects all the data from the CUSTOMERS table based on the customer id i.e. p_customer_id
create or replace function f_ref_cursor (p_customer_id IN number)
return SYS_REFCURSOR as
TYPE t_cursor is REF CURSOR;
v_cursor t_cursor;
open v_cursor FOR
select * from customers
where customer_id = p_customer_id;
RETURN v_cursor;
end;
Simple stuff, now how do you call the function from your BIP report?
Just use a SQL based data sources thus:
select f_ref_cursor(:cust_id) from dualThe cust_id is from a parameter defined in the report definition.
Ref Cursors ... Done!
