Search code examples
sqloraclecursor

Oracle pl/sql reference(return?) cursor/declared cursor

Is there a way to do something like this? Output a cursor's fetched data into a refcursor without stuffing it into a table first?

create or replace procedure someprocedure
(
rc1 in out adv_refcur_pkg.rc)  -- this is defined below
as

v_class_year varchar(10);

cursor c1
is
select distinct e.pref_class_year
from entity e
where e.pref_class_year between i_start_class and i_end_class;

begin
open c1;
loop
fetch c1 into v_class_year;
EXIT WHEN c1%NOTFOUND;

end loop;
close c1;

open rc1 for select v_class_year from dual;
end;

here is the refcursor's declaration

CREATE OR REPLACE PACKAGE ADVANCE.adv_refcur_pkg
AS
TYPE RC IS REF CURSOR;
END adv_refcur_pkg;
Solution

  • According with this example, yes, it's possible:

    https://forums.oracle.com/thread/696634