CODEWITHSUNDEEP
oracle-databaseplsqlcursor
TBose
TBose

Reputation: 115

How to open a sys_refcursor with values from a normal cursor?

Can I open a sys_refcursor with value from a normal cursor?

create or replace procedure test(C1 out sys_refcursor)
  Lv_c1 as
    Select * from table;
Begin
  Open C1 for select * from lv_c1;
End;

Upvotes: 2

Views: 6969

Answers (1)

0xdb
0xdb

Reputation: 3697

No, you cannot. "Normal" cursor is a PL/SQL variable, so it cannot be used in SQL query.

But it's possible to open a cursor for a result set of a cursor variable:

create or replace package pack as 
    cursor cur is 
        select rownum attr_1 from dual connect by level<=3;
    type rset is table of cur%rowtype;     
    procedure getCursor (rc out sys_refcursor);
end;
/
create or replace package body pack as 
    procedure getCursor (rc out sys_refcursor) is
        rs rset; 
    begin
        open cur;
        fetch cur bulk collect into rs;
        close cur;    
        open rc for select * from table (rs);
    end;
end;
/

Execution and the result:

var rc refcursor
exec pack.getCursor (:rc)  

ATTR_1
--------
row1
row2
row3

Upvotes: 5

Related Questions