Oracle Blog

from pixel-perfect to interactive reporting...

Ref Cursor Datasources

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:

We are doing a conversion of Crystal Reports to BI Publisher. The Crystal reports make use of Reference Cursors. I am going to exploit this technique and it is going to save us a lot of work!! Thanks for this post!

About

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!