SQL> create table myex
  2  (
  3    qid      number,
  4    lid      number,
  5    myname   varchar2 (20),
  6    status   varchar2 (30)
  7  );
 
Table created.
 
SQL>
SQL> insert into myex values (1, 1, 'uu', null);
 
1 row created.
 
SQL>
SQL> commit;
 
Commit complete.
 
SQL>
SQL>
SQL> create or replace package mypack
  2  is
  3    procedure p_get (in_qid myex.qid%type, out_mycur out sys_refcursor);
  4  end mypack;
  5  / 
 
Package created.
 
SQL>
SQL>
SQL> create or replace package body mypack
  2  is
  3    procedure p_get (in_qid myex.qid%type, out_mycur out sys_refcursor)
  4    is
  5      v_num   myex.lid%type;
  6
  7      cursor c1 (in_num number)
  8      is
  9        select lid, myname
 10          from myex
 11         where lid = in_num
 12           and status = 'None';
 13    begin
 14      if out_mycur%isopen
 15      then
 16        close out_mycur;
 17      end if;
 18
 19      select lid
 20        into v_num
 21        from myex
 22       where qid = in_qid;
 23
 24      for r1 in c1 (v_num)
 25      loop
 26        open out_mycur for select r1.myname from dual;
 27      end loop;
 28
 29      if not out_mycur%isopen
 30      then
 31        open out_mycur for select null from dual;
 32      end if;
 33    end;
 34  end;
 35  / 
 
Package body created.
 
SQL>
SQL> var out_mycur refcursor;
SQL>
SQL> begin
  2    mypack.p_get (1, :out_mycur);
  3  end;
  4  / 
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> print
 
N
-
 
 
1 row selected.
 
SQL>