SQL & PL/SQL

create table myex(qid number, lid number, myname varchar2(20), status varchar2(30));

insert into myex values(1,1,'uu',null);

commit;



CREATE OR REPLACE PACKAGE mypack
IS

PROCEDURE p_get (in_qid myex.qid%TYPE, out_mycur OUT SYS_REFCURSOR);

end mypack;
/



CREATE OR REPLACE PACKAGE BODY mypack
IS
PROCEDURE p_get (in_qid myex.qid%TYPE, out_mycur OUT SYS_REFCURSOR)
IS
v_name VARCHAR2 (100);
v_num NUMBER;

CURSOR c1 (in_num NUMBER)
IS
SELECT lid, myname
FROM myex
WHERE lid = in_num and status = 'None';
BEGIN
IF out_mycur%ISOPEN
THEN
CLOSE out_mycur;
END IF;

select lid
into v_num
from myex
where qid = in_qid;

FOR r1 IN c1 (v_num)
LOOP
IF c1%ROWCOUNT = 0
THEN
OPEN out_mycur FOR SELECT NULL FROM DUAL;
END IF;

EXIT WHEN c1%NOTFOUND;
v_name := r1.myname;

OPEN out_mycur FOR SELECT v_name FROM DUAL;
END LOOP;
END;
END;
/

Note

Note the one record in the table does not satisfy the cursor query criteria, So I try to pass in 1 to see what happens.


In sqlplus:


var out_mycur refcursor;

begin

mypack.p_get(1,:out_mycur);
end;
/

print out_mycur;

ORA-24338: statement handle not executed