Is there a way of selecting the contents of a ref cursor as part of a SQL Select statement?

Question

I'd like to be able to take a ref cursor and transform it by selecting from it into another ref cursor. For example, something like this:

begin
declare c_cur sys_refcursor;
        c_cur2 sys_refcursor;

open c_cur for 
  select 1 from dual;

open c_cur2 for
  select 1
    from c_cur
   where 1 = 2;

end;
/

Is it possible in Oracle to select FROM the results of a ref cursor, in a SELECT statement?

Answer 1

You can't do exactly what you wanting to do there, however the following may fit your needs. Basically two cursor for loops one of which takes in data from the first cursor.

declare

cursor my_cur_1 is
select foo from bar;

cursor my_cur_2 (my_foo bar.foo%TYPE) is
select foo2 from bar2 where bar2.foo = my_foo;

begin

for t in my_cur_1 LOOP

  for s in my_cur_2(t.foo) LOOP
  -- do some stuff here with data from both.
  end loop;
end loop;

end;

Answer 2

I'm not sure why you'd want to. I think what you're looking for is something more along the lines of bulk collect. This will allow you to fetch your results into an collection or table and then you can select from those in the second query. Better yet, refactor this into a single more elegant query.

Answer 3

Looks like you just want to build a ref_cursor dynamically.

You can do this like this:

FUNCTION test_dyn_ref RETURN sys_refcursor IS
  l_cur2  sys_refcursor;
  l_query varchar2(4000);
begin
  l_query := 'select 1 from dual ';
  l_query := l_query||' where 1 = 2 ';
  --
  open l_cur2 for l_query;
  return l_cur2;
end;