Hi, This post is to show you a sample example on how to use ref cursor in Oracle apps R12 which will be helpful for beginners. Below code shows an example of using Ref cursor.
CREATE OR REPLACE PROCEDURE RETURNING_REFCURSOR(
P_EMPNO EMP05.EMPNO%TYPE,
L_REFCURSOR OUT SYS_REFCURSOR) IS
L_ENAME EMP05.ENAME%TYPE;
L_DNAME DEPT05.DNAME%TYPE;
BEGIN
OPEN L_REFCURSOR FOR SELECT E.ENAME,D.DNAME FROM EMP05 E,DEPT05 D WHERE E.DEPTNO = D.DEPTNO AND E.EMPNO = P_EMPNO;
LOOP
FETCH L_REFCURSOR INTO L_ENAME,L_DNAME;
EXIT WHEN L_REFCURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(L_ENAME||' '||L_DNAME);
END LOOP;
CLOSE L_REFCURSOR;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END RETURNING_REFCURSOR;
/
SET SERVEROUTPUT ON
VARIABLE G_REF REFCURSOR
VARIABLE G_EMPNO NUMBER
EXECUTE :G_EMPNO := 7839
EXECUTE RETURNING_REFCURSOR(:G_EMPNO,:G_REF)
Happy learning!!
