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;
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)
);
(
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;
(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.
Posts
