Kashif Masood Hashmi Blog

Using Reference Cursor For Creating Record Group

CREATE THE FORM WITH ONE TEXT ITEM AND ONE LIST ITEM. 

@LIST ITEM ELEMENT_LIST SET TO BE NULL.

IN PROGRAM UINT CREATE THE FOLLOWING.

PACKAGE pkg_ref_cursor IS

  TYPE user_CURSOR IS REF CURSOR;

END;

PROCEDURE GET_DEPT_JOBS(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 DISTINCT INITCAP(JOB) JOBS, JOB FROM SCOTT.EMP

                    WHERE DEPTNO=P_DEPTNO;

IO_CURSOR := V_CURSOR;

EXCEPTION

                WHEN OTHERS THEN

                RAISE FORM_TRIGGER_FAILURE;

END;

CREATE ONE PUSH BUTTON AND APPLY FOLLOWING CODE

DECLARE

  RG_ID    RECORDGROUP;             RG_NAME VARCHAR2(20) := 'RG_DEPT';

  GC_ID1  GROUPCOLUMN;             GC_ID2                 GROUPCOLUMN;

  ROW_CNT NUMBER := 0;             MY_CURSOR PKG_REF_CURSOR.USER_CURSOR;

  V_JOBS VARCHAR2(10);              V_JOB  VARCHAR2(10);

BEGIN

--PROCEDURE GETTING ROWS

GET_DEPT_JOBS(:TI_DEPTNO,MY_CURSOR);

RG_ID := FIND_GROUP(RG_NAME);

IF NOT ID_NULL(RG_ID) THEN     

    DELETE_GROUP(RG_ID);               

END IF;

RG_ID := CREATE_GROUP(RG_NAME);

IF ID_NULL(RG_ID) THEN

   MESSAGE('ERR: CREATING RECORD GROUP '||RG_NAME);

   RAISE FORM_TRIGGER_FAILURE;               

END IF;

GC_ID1 := ADD_GROUP_COLUMN(RG_ID, 'HEAD', CHAR_COLUMN,20);

GC_ID2 := ADD_GROUP_COLUMN(RG_ID, 'JOB',CHAR_COLUMN,05);

LOOP

  ROW_CNT :=ROW_CNT+1;

  ADD_GROUP_ROW(RG_ID, END_OF_GROUP);    

  FETCH MY_CURSOR INTO V_JOBS, V_JOB;         

  EXIT WHEN MY_CURSOR%NOTFOUND;

  SET_GROUP_CHAR_CELL(GC_ID1, ROW_CNT, V_JOBS);   

  SET_GROUP_CHAR_CELL(GC_ID2, ROW_CNT, V_JOB);

END LOOP;

POPULATE_LIST('JOB_LIST', RG_ID);

END;