Kashif Masood Hashmi Blog

Using Refcursor Getting Details

CREATE ONE FORM WITH ONE NON-DATABASE BLOCK HAVE ONE TEXT ITEM, AND ONE PUSH BUTTON.

CREATE SCOTT.EMP DATA BLOCK IN TABULAR STYLE ON SAME FORM.

DEFINE THE FOLLOWING

IN PROGRAM UNIT

CREATE PACKAGE

PACKAGE PKG_REF_CURSOR IS

  TYPE USER_CURSOR IS REF CURSOR;

END;

CREATE THE FOLLOWING PROCEDURE

PROCEDURE GET_DEPT_DETL (P_DEPTNO   IN NUMBER,

                        io_cursor  OUT PKG_REF_CURSOR.user_CURSOR) IS

 V_CURSOR PKG_REF_CURSOR.user_CURSOR;

BEGIN

 OPEN V_CURSOR FOR SELECT * FROM SCOTT.EMP

                    WHERE DEPTNO=P_DEPTNO;

IO_CURSOR := V_CURSOR;

EXCEPTION

                WHEN OTHERS THEN

                RAISE FORM_TRIGGER_FAILURE;

END;

ON PUSH BUTTON

DECLARE

                ROW_CNT NUMBER := 0;

                MY_CURSOR PKG_REF_CURSOR.USER_CURSOR;

BEGIN

--PROCEDURE GETTING ROWS

GET_DEPT_DETL(:TI_DEPTNO,MY_CURSOR);

GO_BLOCK('EMP');

                LOOP

                  ROW_CNT :=ROW_CNT+1;

                  FETCH MY_CURSOR

                 INTO :EMPNO,:ENAME,:JOB,:MGR,:HIREDATE,:SAL,:COMM,:DEPTNO;   

                  NEXT_RECORD;

                  EXIT WHEN MY_CURSOR%NOTFOUND;

                END LOOP;

END;

Run the Form.