Dynamic Ref Cursor Examples

CREATE OR REPLACE PROCEDURE REFCUR(L_P_EMPNO IN EMP.EMPNO%TYPE) IS
TYPE EmpCurTyp IS REF CURSOR;
L_REFCURSOR_CHECK EmpCurTyp;
L_V_SQL_STRING VARCHAR2(200);
v_ename emp.ename%TYPE;
v_dname dept.dname%TYPE;
BEGIN
L_V_SQL_STRING := ‘SELECT E.ENAME, D.DNAME
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND E.EMPNO = :L_P_EMPNO’;

OPEN L_REFCURSOR_CHECK FOR L_V_SQL_STRING USING l_p_empno;
LOOP
FETCH l_refcursor_check INTO v_ename, v_dname;
EXIT WHEN l_refcursor_check%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (v_ename || ‘ works in ‘ || v_dname);
END LOOP;

CLOSE L_REFCURSOR_CHECK;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END REFCUR;

Advertisements