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

Question

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;

Simply open the rc1 cursor for query used in the c1 cursor definition. — Nick Krasnov
– Nick Krasnov, Commented Oct 17, 2013 at 13:29

VancleiP · Accepted Answer · 2013-10-17 13:31:56Z

1

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

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

answered Oct 17, 2013 at 13:31

VancleiP

6574 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DCookie · Accepted Answer · 2013-10-17 13:51:08Z

0

Why go to the trouble of doing that when you can simply pass the cursor itself?

create or replace procedure someprocedure
(
rc1 in out adv_refcur_pkg.rc)  -- this is defined below
as
begin
open rc1 for
   select distinct e.pref_class_year
     from entity e
    where e.pref_class_year between i_start_class and i_end_class;
end;

When you call "someprocedure", you have an open cursor you can then fetch from:

BEGIN
...
  someprocedure(TheCursor);
  LOOP
    fetch TheCursor into v_class_year;
    exit when TheCursor%NOTFOUND;
    ...
  END LOOP;
  CLOSE TheCursor;
...
END;

answered Oct 17, 2013 at 13:51

DCookie

43.7k11 gold badges88 silver badges93 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related