Business Intelligence – Oracle

Oracle BI EE 10.1.3.2/3 – Ref Cursors and Pipelined functions

Posted by Venkatakrishnan J on September 21, 2007

Another interesting question that generally comes up is whether Oracle BI EE supports Ref Cursors. Well, currently only BI Publisher supports the use of ref cursors to pass the data to the output (Remember, BI Publisher uses jdbc connections and jdbc supports ref cursors.). I tried using the following ref cursor function on both BI Publisher and BI EE. Lets see what the results are and of course lets see what is the workaround available for OBI EE. To start with i created a function that basically returns a ref cursor of the dept table in scott schema.

create or replace function refcursor_dept return sys_refcursor as
TYPE v_cursor is REF CURSOR;
p_cursor v_cursor;
begin
open p_cursor for
select * from dept;
return p_cursor;
end;

      

In BI Publisher this would work like a breeze. All one would have to do is to call the function. Tim Dexter has already blogged about this. One can get more details here.

Now, lets try this in BI EE. I will be testing this using the direct database requests option on BI EE since it provides an easier way of testing things out. If you have not set direct database requests, i have blogged about it here showing you how to set it up. I have created a connection pool that basically points to my scott schema.

      

Now, lets fire the query using direct database requests using the connection pool that i had created earlier.

      

As you see, we get an error saying that this data type is not supported (this is the first time i am trying this out. If anyone has found a way of directly calling reference cursors from OBI EE please do let me know. I would be more than happy to share it here). So, for users who want to leverage their existing ref cursor functions, one option is to use pipelined functions. Lets see for the above example, how one can go about creating a pipelined function and of course how one can call them from OBI EE.

The first step is to create an object type that would basically contain all the columns of the dept table.

create or replace type dept_objecttype as object
(
DEPTNO NUMBER,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
);

Once this is done, lets create a table type of the above object.

create or replace type tabletype_dept as table of dept_objecttype;

Once this is done, lets create a pipelined function that would basically take in the ref cursor function that we created earlier as an argument.

create or replace function pipelined_dept
(p_cursor in sys_refcursor)
return tabletype_dept
PIPELINED
as
v_records dept%rowtype;
begin
loop
fetch p_cursor into v_records;
exit when (p_cursor%notfound);
pipe row(
dept_objecttype(v_records.deptno,
v_records.dname,
v_records.loc) );
end loop;
return;
end;

Now lets test this function from sqlplus.

      

Once this is done, lets test it out in BI EE.

      

      

For more detailed explanation on pipelined functions, i would recommend Tom Kyte’s article here. And yes of course, if you have other methods of calling ref cursors, feel free to put them in the comments section.

Advertisements

2 Responses to “Oracle BI EE 10.1.3.2/3 – Ref Cursors and Pipelined functions”

  1. krishna said

    Hi,

    Your post about REF CURSORS is very useful.
    I am converting an RDF which is using ref cursor. When I run the report, it is a blank report.
    Can you explain how we can use refcursor in the data template.

    Thanks,
    Krishna

  2. Somnath De said

    Hi,

    There is some issue in this approach. The first thing is that how can I pass a dashboard prompt value to the input to the function. One approach could be -> set the Presentation Variable from Prompt, then set session variable using presentation variable and finally session variable to function input. The problem in this approach is that the presentation variable can be used only with a limited dashboard prompt type. Most of the other cases they are not applicable at all. Could you please provide a way where we can use this approach with multipale values or between clauses as well.

    Regards,
    Somnath

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: