Oracle Fusion Middleware Blog

Deutsche Informationen rund um Oracle Fusion Middleware

Using Ref Cursors as a Data Source in Oracle BI Publisher

leave a comment »

Sometimes there is a requirement to use a referential cursor in the SQL query of a BI Publisher report.
You can use ref cursors which return a single row or multiple rows.

Here is an example of a ref cursor returning a single row:
In the database create a function REF_CURSOR_CUST in the demo schema OE

create or replace function REF_CURSOR_CUST (p_customer_id IN number)
return SYS_REFCURSOR as
TYPE t_cursor is REF CURSOR;
v_cursor t_cursor;
begin
open v_cursor FOR
select customer_id, cust_first_name, cust_last_name, credit_limit,
cust_email, gender, income_level
from customers
where customer_id = p_customer_id;
RETURN v_cursor;
end REF_CURSOR_CUST;

In the data definition of the BI Publisher report you can now use the ref cursor in your query:
select ref_cursor_cust(:cust_id) from dual

Because of a small bug this query will return no rows if you test it against the BI Publisher server 10.1.3.3.1.
Just add something to the select-list and it will work as expected:
select null, ref_cursor_cust(:cust_id) from dual

An example which returns multiple rows will work in a similar way:
In the database create a function REF_CURSOR_DEPT in the demo schema OE

create or replace function REF_CURSOR_DEPT (p_deptno IN number)
return SYS_REFCURSOR as
TYPE t_cursor is REF CURSOR;
v_cursor t_cursor;
begin
open v_cursor FOR
select * from employees
where department_id = p_deptno;
RETURN v_cursor;
end REF_CURSOR_DEPT;

In the data definition of the BI Publisher report you can now use the ref cursor in your query:
select null, ref_cursor_dept(:deptno) from dual
It will return a number of rows depending from the parameter deptno which has to be selected before the query is executed.

<JM>

Written by fmtechteam

06/01/2008 um 19:31

Veröffentlicht in BI Publisher, Jürgen Menge

Schreibe einen Kommentar

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

%d Bloggern gefällt das: