CREATE TABLE PATENT (ID  NUMBER, QUERY VARCHAR2(90),PROCEDURE_NAME VARCHAR2(90),FLAG VARCHAR2(90));
INSERT INTO  PATENT VALUES (101,'SAMPLE_QUERY','PRC1','1'); 
COMMIT;

CREATE OR REPLACE
PROCEDURE PROC1
AS
TYPE T_CURSOR
IS
  REF
  CURSOR;
    V_QUERY PATENT.QUERY%TYPE;
    PROC_NM PATENT.PROCEDURE_NAME%TYPE;
    VID PATENT.ID%TYPE;
    CURSOR C
    IS
      SELECT QUERY FROM PATENT WHERE FLAG=1;
    CUR_REF T_CURSOR;
  BEGIN
    OPEN C;
    LOOP
      FETCH C INTO V_QUERY;
      EXIT
    WHEN C%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Using normal cursor : '||V_QUERY);
    END LOOP;
    CLOSE C;
    OPEN CUR_REF FOR SELECT QUERY FROM PATENT WHERE FLAG=1;
    LOOP
      FETCH CUR_REF INTO V_QUERY;
      EXIT
    WHEN CUR_REF%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Using Refcursor : '||V_QUERY);
    END LOOP;
    CLOSE C;
  END PROC1;

CREATE TABLE EMP_1 (EMPNO NUMBER PRIMARY KEY, ENAME VARCHAR2(90), DEPTNO NUMBER REFERENCING DEPT_1(DEPTNO));
  CREATE TABLE DEPT_1 (DEPTNO NUMBER PRIMARY KEY, DEPT_NAME VARCHAR2(90));
  CREATE TABLE SALARY_1 (EMPNO NUMBER REFERENCING EMP_1(EMPNO), SALARY NUMBER);
  INSERT INTO EMP_1 VALUES (101, 'ADAMN', 1);
  INSERT INTO EMP_1 VALUES (102, 'RICKY', 2);
  INSERT INTO EMP_1 VALUES (103, 'CLARK', 3);
  INSERT INTO EMP_1 VALUES (104, 'PATIL', 2);
  
  INSERT INTO DEPT_1 VALUES (1, 'MANAGER');
  INSERT INTO DEPT_1 VALUES (2, 'ACCOUNT');
  INSERT INTO DEPT_1 VALUES (3, 'HR');
 
  INSERT INTO SALARY_1 VALUES (101, 10000);
  INSERT INTO SALARY_1 VALUES (102, 2400);
  INSERT INTO SALARY_1 VALUES (103, 5000);
  INSERT INTO SALARY_1 VALUES (104, 1500);

CREATE TABLE PATENT (ID NUMBER, QUERY VARCHAR2(1000), PROC VARCHAR2(100),FLG VARCHAR2(1));
                       DROP TABLE PATENT;
  
  INSERT INTO PATENT VALUES  (10,'SELECT EMPNO, ENAME, DEPTNO FROM EMP_1','PRC1','1');
  INSERT INTO PATENT VALUES  (20,'SELECT DEPTNO, DEPT_NAME FROM DEPT_1','PRC2','1');
  INSERT INTO PATENT VALUES  (30,'SELECT EMPNO, SALARY FROM SALARY_1','PRC3','1');
/
create or replace PROCEDURE PROC1 AS
  TYPE T_CURSOR IS REF CURSOR;
  CUR_REF T_CURSOR;
  
  TYPE REC_EMP IS RECORD (EMPNO EMP_1.EMPNO%TYPE, ENAME EMP_1.ENAME%TYPE, DEPTNO EMP_1.DEPTNO%TYPE);
  LV_REC_EMP REC_EMP;
  TYPE REC_DEPT IS RECORD (DEPTNO DEPT_1.DEPTNO%TYPE, DEPT_NAME DEPT_1.DEPT_NAME%TYPE );
  LV_REC_DEPT REC_DEPT;
  TYPE REC_SAL IS RECORD (EMPNO SALARY_1.EMPNO%TYPE, SALARY SALARY_1.SALARY%TYPE);
  LV_REC_SAL REC_SAL;
  
  CURSOR C1 IS SELECT QUERY,PROC FROM PATENT WHERE FLG='1';
  V_QUERY PATENT.QUERY%TYPE;
  PROC PATENT.PROC%TYPE;
  
BEGIN
    OPEN C1;
    LOOP
      FETCH C1 INTO V_QUERY,PROC;
      EXIT WHEN C1%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE('Using normal cursor : '||V_QUERY);
          OPEN CUR_REF FOR V_QUERY;
          LOOP
          IF PROC ='PRC1' THEN
              FETCH CUR_REF INTO LV_REC_EMP;
              EXIT WHEN CUR_REF%NOTFOUND;
              DBMS_OUTPUT.PUT_LINE('Using Refcursor : '||LV_REC_EMP.EMPNO||' | '||LV_REC_EMP.ENAME||' | '||LV_REC_EMP.DEPTNO);
          ELSIF PROC ='PRC2' THEN
              FETCH CUR_REF INTO LV_REC_DEPT;
              EXIT WHEN CUR_REF%NOTFOUND;
              DBMS_OUTPUT.PUT_LINE('Using Refcursor : '||LV_REC_DEPT.DEPTNO||' | '||LV_REC_DEPT.DEPT_NAME);
          ELSIF PROC ='PRC3' THEN
              FETCH CUR_REF INTO LV_REC_SAL;
              EXIT WHEN CUR_REF%NOTFOUND;
              DBMS_OUTPUT.PUT_LINE('Using Refcursor : '||LV_REC_SAL.EMPNO||' | '||LV_REC_SAL.SALARY);
          END IF;
          END LOOP;
    END LOOP;
    CLOSE C1;
  END PROC1;
/
--Output for the above procedure
Using normal cursor : SELECT EMPNO, ENAME, DEPTNO FROM EMP_1
Using Refcursor : 101 | ADAMN | 1
Using Refcursor : 102 | RICKY | 2
Using Refcursor : 103 | CLARK | 3
Using Refcursor : 104 | PATIL | 2
Using normal cursor : SELECT DEPTNO, DEPT_NAME FROM DEPT_1
Using Refcursor : 1 | MANAGER
Using Refcursor : 2 | ACCOUNT
Using Refcursor : 3 | HR
Using normal cursor : SELECT EMPNO, SALARY FROM SALARY_1
Using Refcursor : 101 | 10000
Using Refcursor : 102 | 2400
Using Refcursor : 103 | 5000
Using Refcursor : 104 | 1500