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:

select f_ref_cursor(:cust_id) from dual
The cust_id is from a parameter defined in the report definition.

Ref Cursors ... Done!

Comments:

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!

Posted by Jim O'Connor on November 27, 2009 at 02:05 AM MST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

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!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today