Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

How to fetch data from ref cursor in Oracle using for loop?

Leave a Comment / COLLECTIONS, USEFUL COLLECTION QUERIES / By

How to fetch data from ref cursor in Oracle using for loop?

We will examine different ways to fetch data from ref cursor.

We will use SYS_REFCURSOR as ref cursor variable as its a week type of cursor.

It will allow us to fetch data as per our format.

Fetching data from ref cursor using a plsql block:

Using simple plsql variable:

First we will use simple plsql variable to fetch data from a ref cursor.

--How to fetch data from ref cursor in Oracle using for loop? : Example

SET SERVEROUTPUT ON;
DECLARE
  V_EMPLOYEE_ID    EMPLOYEES.EMPLOYEE_ID%TYPE := NULL;
  V_FIRST_NAME     EMPLOYEES.FIRST_NAME%TYPE := NULL;
  V_LAST_NAME      EMPLOYEES.LAST_NAME%TYPE := NULL;
  V_SALARY         EMPLOYEES.SALARY%TYPE := NULL;
  V_JOB_ID         EMPLOYEES.JOB_ID%TYPE := NULL;
  --
  V_CURSOR SYS_REFCURSOR;
BEGIN
  OPEN V_CURSOR FOR
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         SALARY,
         JOB_ID
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  --
  LOOP
    FETCH V_CURSOR INTO V_EMPLOYEE_ID,
                        V_FIRST_NAME,
                        V_LAST_NAME,
                        V_SALARY,
                        V_JOB_ID;
    EXIT WHEN V_CURSOR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE -> ID -> '||V_EMPLOYEE_ID||' -> NAME -> '||V_FIRST_NAME||' '||V_LAST_NAME||' - > SALARY -> '||V_SALARY||' -> JOB_ID ->'||V_JOB_ID);
  END LOOP;
  CLOSE V_CURSOR;
END;
/
--How to fetch data from ref cursor in Oracle using for loop? : Output

PL/SQL procedure successfully completed.

EMPLOYEE -> ID -> 208 -> NAME -> Jhon Smith - > SALARY -> 1000 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 114 -> NAME -> Den Raphaely - > SALARY -> 11000 -> JOB_ID ->PU_MAN
EMPLOYEE -> ID -> 115 -> NAME -> Alexander Khoo - > SALARY -> 3100 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 116 -> NAME -> Shelli Baida - > SALARY -> 2900 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 117 -> NAME -> Sigal Tobias - > SALARY -> 2800 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 118 -> NAME -> Guy Himuro - > SALARY -> 2600 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 119 -> NAME -> Karen Colmenares - > SALARY -> 2500 -> JOB_ID ->PU_CLERK
Extracting data using plsql record variable:

Now using record variable to fetch data from a ref cursor:

--How to fetch data from ref cursor in Oracle using for loop? : Example

DECLARE
  TYPE T_REC IS RECORD
  (EMPLOYEE_ID    EMPLOYEES.EMPLOYEE_ID%TYPE,
   FIRST_NAME     EMPLOYEES.FIRST_NAME%TYPE,
   LAST_NAME      EMPLOYEES.LAST_NAME%TYPE,
   SALARY         EMPLOYEES.SALARY%TYPE,
   JOB_ID         EMPLOYEES.JOB_ID%TYPE
  );
  --
  V_REC T_REC;
  --
  V_CURSOR SYS_REFCURSOR;
BEGIN
  OPEN V_CURSOR FOR
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         SALARY,
         JOB_ID
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  --
  LOOP
    FETCH V_CURSOR INTO V_REC;
    EXIT WHEN V_CURSOR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE -> ID -> '||V_REC.EMPLOYEE_ID||' -> NAME -> '||V_REC.FIRST_NAME||' '||V_REC.LAST_NAME||' - > SALARY -> '||V_REC.SALARY||' -> JOB_ID ->'||V_REC.JOB_ID);
  END LOOP;
  CLOSE V_CURSOR;
END;
/
--How to fetch data from ref cursor in Oracle using for loop? : Output

PL/SQL procedure successfully completed.

EMPLOYEE -> ID -> 208 -> NAME -> Jhon Smith - > SALARY -> 1000 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 114 -> NAME -> Den Raphaely - > SALARY -> 11000 -> JOB_ID ->PU_MAN
EMPLOYEE -> ID -> 115 -> NAME -> Alexander Khoo - > SALARY -> 3100 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 116 -> NAME -> Shelli Baida - > SALARY -> 2900 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 117 -> NAME -> Sigal Tobias - > SALARY -> 2800 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 118 -> NAME -> Guy Himuro - > SALARY -> 2600 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 119 -> NAME -> Karen Colmenares - > SALARY -> 2500 -> JOB_ID ->PU_CLERK
Fetching data Using collection:

Using collection to fetch data from ref cursor:

--How to fetch data from ref cursor in Oracle using for loop? : Example

DECLARE
  TYPE T_REC IS RECORD
  (EMPLOYEE_ID    EMPLOYEES.EMPLOYEE_ID%TYPE,
   FIRST_NAME     EMPLOYEES.FIRST_NAME%TYPE,
   LAST_NAME      EMPLOYEES.LAST_NAME%TYPE,
   SALARY         EMPLOYEES.SALARY%TYPE,
   JOB_ID         EMPLOYEES.JOB_ID%TYPE
  );
  --
  TYPE T_REFTAB IS TABLE OF T_REC INDEX BY PLS_INTEGER;
  V_REFTAB T_REFTAB;
  --
  V_CURSOR SYS_REFCURSOR;
BEGIN
  OPEN V_CURSOR FOR
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         SALARY,
         JOB_ID
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  --
  FETCH V_CURSOR BULK COLLECT INTO V_REFTAB;
  FOR I IN 1..V_REFTAB.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE -> ID -> '||V_REFTAB(I).EMPLOYEE_ID||' -> NAME -> '||V_REFTAB(I).FIRST_NAME||' '||V_REFTAB(I).LAST_NAME||' - > SALARY -> '||V_REFTAB(I).SALARY||' -> JOB_ID ->'||V_REFTAB(I).JOB_ID);
  END LOOP;
  CLOSE V_CURSOR;
END;
/
--How to fetch data from ref cursor in Oracle using for loop? : Output

PL/SQL procedure successfully completed.

EMPLOYEE -> ID -> 208 -> NAME -> Jhon Smith - > SALARY -> 1000 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 114 -> NAME -> Den Raphaely - > SALARY -> 11000 -> JOB_ID ->PU_MAN
EMPLOYEE -> ID -> 115 -> NAME -> Alexander Khoo - > SALARY -> 3100 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 116 -> NAME -> Shelli Baida - > SALARY -> 2900 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 117 -> NAME -> Sigal Tobias - > SALARY -> 2800 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 118 -> NAME -> Guy Himuro - > SALARY -> 2600 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 119 -> NAME -> Karen Colmenares - > SALARY -> 2500 -> JOB_ID ->PU_CLERK
Usage of BULK COLLECT and FORALL:

Using BULK COLLECT and FORALL to fetch data from a ref cursor:

Creating a test table to test the BULK COLLECT and FORALL scenario. As we know that for FORALL DML is mandatory. So we will insert data into test table.

CREATE TABLE EMPLOYEES_REF_TEST
AS
SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         SALARY,
         JOB_ID
FROM EMPLOYEES
WHERE 1 = 2;
--How to fetch data from ref cursor in Oracle using for loop? : Example

DECLARE
  TYPE T_REC IS RECORD
  (EMPLOYEE_ID    EMPLOYEES.EMPLOYEE_ID%TYPE,
   FIRST_NAME     EMPLOYEES.FIRST_NAME%TYPE,
   LAST_NAME      EMPLOYEES.LAST_NAME%TYPE,
   SALARY         EMPLOYEES.SALARY%TYPE,
   JOB_ID         EMPLOYEES.JOB_ID%TYPE
  );
  --
  TYPE T_REFTAB IS TABLE OF T_REC INDEX BY PLS_INTEGER;
  V_REFTAB T_REFTAB;
  --
  V_CURSOR SYS_REFCURSOR;
BEGIN
  OPEN V_CURSOR FOR
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         SALARY,
         JOB_ID
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  --
  FETCH V_CURSOR BULK COLLECT INTO V_REFTAB;
  FORALL I IN 1..V_REFTAB.COUNT
    INSERT INTO EMPLOYEES_REF_TEST(EMPLOYEE_ID,
                                   FIRST_NAME,
                                   LAST_NAME,
                                   SALARY,
                                   JOB_ID
                                  )
    VALUES(V_REFTAB(I).EMPLOYEE_ID,
           V_REFTAB(I).FIRST_NAME,
           V_REFTAB(I).LAST_NAME,
           V_REFTAB(I).SALARY,
           V_REFTAB(I).JOB_ID
          );
  CLOSE V_CURSOR;
  --
  FOR I IN (SELECT * FROM EMPLOYEES_REF_TEST)
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE -> ID -> '||I.EMPLOYEE_ID||' -> NAME -> '||I.FIRST_NAME||' '||I.LAST_NAME||' - > SALARY -> '||I.SALARY||' -> JOB_ID ->'||I.JOB_ID);
  END LOOP;
END;
/
--How to fetch data from ref cursor in Oracle using for loop? : Output

PL/SQL procedure successfully completed.

EMPLOYEE -> ID -> 208 -> NAME -> Jhon Smith - > SALARY -> 1000 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 114 -> NAME -> Den Raphaely - > SALARY -> 11000 -> JOB_ID ->PU_MAN
EMPLOYEE -> ID -> 115 -> NAME -> Alexander Khoo - > SALARY -> 3100 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 116 -> NAME -> Shelli Baida - > SALARY -> 2900 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 117 -> NAME -> Sigal Tobias - > SALARY -> 2800 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 118 -> NAME -> Guy Himuro - > SALARY -> 2600 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 119 -> NAME -> Karen Colmenares - > SALARY -> 2500 -> JOB_ID ->PU_CLERK

Fetching data from ref cursor using a stored procedure:

Using simple procedure:

First we will create a table for exception handling:

CREATE TABLE EMP_SAVE_EXCEPTION_TAB
(
 ERROR_ID       NUMBER GENERATED ALWAYS AS IDENTITY,
 ERROR_CODE     VARCHAR2(1000),
 ERROR_MSG      VARCHAR2(4000),
 ERROR_DATE     DATE
);

Simple format of procedure:

--How to fetch data from ref cursor in Oracle using for loop? : Example

CREATE OR REPLACE PROCEDURE SP_REF_CUR_TEST(P_DEPTNO DEPARTMENTS.DEPARTMENT_ID%TYPE)
IS
  TYPE T_REC IS RECORD
  (EMPLOYEE_ID    EMPLOYEES.EMPLOYEE_ID%TYPE,
   FIRST_NAME     EMPLOYEES.FIRST_NAME%TYPE,
   LAST_NAME      EMPLOYEES.LAST_NAME%TYPE,
   SALARY         EMPLOYEES.SALARY%TYPE,
   JOB_ID         EMPLOYEES.JOB_ID%TYPE
  );
  --
  TYPE T_REFTAB IS TABLE OF T_REC INDEX BY PLS_INTEGER;
  V_REFTAB T_REFTAB;
  --
  V_CURSOR SYS_REFCURSOR;
BEGIN
  OPEN V_CURSOR FOR
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         SALARY,
         JOB_ID
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = P_DEPTNO;
  --
  FETCH V_CURSOR BULK COLLECT INTO V_REFTAB;
  FOR I IN 1..V_REFTAB.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE -> ID -> '||V_REFTAB(I).EMPLOYEE_ID||' -> NAME -> '||V_REFTAB(I).FIRST_NAME||' '||V_REFTAB(I).LAST_NAME||' - > SALARY -> '||V_REFTAB(I).SALARY||' -> JOB_ID ->'||V_REFTAB(I).JOB_ID);
  END LOOP;
  CLOSE V_CURSOR;
END;
/
EXEC SP_REF_CUR_TEST(30);
--How to fetch data from ref cursor in Oracle using for loop? : Output

PL/SQL procedure successfully completed.

EMPLOYEE -> ID -> 208 -> NAME -> Jhon Smith - > SALARY -> 1000 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 114 -> NAME -> Den Raphaely - > SALARY -> 11000 -> JOB_ID ->PU_MAN
EMPLOYEE -> ID -> 115 -> NAME -> Alexander Khoo - > SALARY -> 3100 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 116 -> NAME -> Shelli Baida - > SALARY -> 2900 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 117 -> NAME -> Sigal Tobias - > SALARY -> 2800 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 118 -> NAME -> Guy Himuro - > SALARY -> 2600 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 119 -> NAME -> Karen Colmenares - > SALARY -> 2500 -> JOB_ID ->PU_CLERK
Using collection:

Complex format of procedure:

CREATE OR REPLACE PROCEDURE SP_REF_CUR_TEST(P_DEPTNO DEPARTMENTS.DEPARTMENT_ID%TYPE)
IS
  TYPE T_REC IS RECORD
  (EMPLOYEE_ID    EMPLOYEES.EMPLOYEE_ID%TYPE,
   FIRST_NAME     EMPLOYEES.FIRST_NAME%TYPE,
   LAST_NAME      EMPLOYEES.LAST_NAME%TYPE,
   SALARY         EMPLOYEES.SALARY%TYPE,
   JOB_ID         EMPLOYEES.JOB_ID%TYPE
  );
  --
  TYPE T_REFTAB IS TABLE OF T_REC INDEX BY PLS_INTEGER;
  V_REFTAB T_REFTAB;
  --
  V_CURSOR      SYS_REFCURSOR;
  V_ERROR_CODE  VARCHAR2(100);
  V_ERROR_INDEX VARCHAR2(100);
  --
  PROCEDURE SP_SAVE_EXCEPTION_PROC
  (P_ERROR_CODE VARCHAR2,
   P_ERROR_MSG  VARCHAR2,
   P_ERROR_DATE DATE
  )
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO EMP_SAVE_EXCEPTION_TAB(ERROR_CODE, ERROR_MSG, ERROR_DATE)
    VALUES(P_ERROR_CODE, P_ERROR_MSG, P_ERROR_DATE);
    --
    COMMIT;
  END SP_SAVE_EXCEPTION_PROC;
BEGIN
  DELETE EMPLOYEES_REF_TEST;
  --
  OPEN V_CURSOR FOR
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         SALARY,
         JOB_ID
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = P_DEPTNO;
  --
  LOOP
    FETCH V_CURSOR BULK COLLECT INTO V_REFTAB LIMIT 50;
    EXIT WHEN V_REFTAB.COUNT = 0;
    BEGIN
      FORALL I IN 1..V_REFTAB.COUNT SAVE EXCEPTIONS
      INSERT INTO EMPLOYEES_REF_TEST(EMPLOYEE_ID,
                                     FIRST_NAME,
                                     LAST_NAME,
                                     SALARY,
                                     JOB_ID
                                    )
      VALUES(V_REFTAB(I).EMPLOYEE_ID,
             V_REFTAB(I).FIRST_NAME,
             V_REFTAB(I).LAST_NAME,
             V_REFTAB(I).SALARY,
             V_REFTAB(I).JOB_ID
            );
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -24381 THEN
          FOR I IN 1..SQL%BULK_EXCEPTIONS.COUNT
          LOOP
            V_ERROR_CODE := SQL%BULK_EXCEPTIONS(I).ERROR_CODE;
            V_ERROR_INDEX := SQL%BULK_EXCEPTIONS(I).ERROR_INDEX;
            SP_SAVE_EXCEPTION_PROC(V_ERROR_CODE, 'ERROR AT INDEX NO -> '||V_ERROR_INDEX||' -> '||SQLERRM(V_ERROR_CODE), SYSDATE);
          END LOOP;
        ELSE
          SP_SAVE_EXCEPTION_PROC(SQLCODE, SQLERRM, SYSDATE);
        END IF;
    END;  
  END LOOP;  
  CLOSE V_CURSOR;
  --
  FOR I IN (SELECT * FROM EMPLOYEES_REF_TEST)
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE -> ID -> '||I.EMPLOYEE_ID||' -> NAME -> '||I.FIRST_NAME||' '||I.LAST_NAME||' - > SALARY -> '||I.SALARY||' -> JOB_ID ->'||I.JOB_ID);
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    SP_SAVE_EXCEPTION_PROC(SQLCODE, 'ERROR AT THE END -> '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, SYSDATE);
END;
/
EXEC SP_REF_CUR_TEST(30);
PL/SQL procedure successfully completed.

EMPLOYEE -> ID -> 208 -> NAME -> Jhon Smith - > SALARY -> 1000 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 114 -> NAME -> Den Raphaely - > SALARY -> 11000 -> JOB_ID ->PU_MAN
EMPLOYEE -> ID -> 115 -> NAME -> Alexander Khoo - > SALARY -> 3100 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 116 -> NAME -> Shelli Baida - > SALARY -> 2900 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 117 -> NAME -> Sigal Tobias - > SALARY -> 2800 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 118 -> NAME -> Guy Himuro - > SALARY -> 2600 -> JOB_ID ->PU_CLERK
EMPLOYEE -> ID -> 119 -> NAME -> Karen Colmenares - > SALARY -> 2500 -> JOB_ID ->PU_CLERK

Fetching data from ref cursor using a stored function:

CREATE OR REPLACE FUNCTION FUN_REF_CUR_TEST(P_DEPTNO DEPARTMENTS.DEPARTMENT_ID%TYPE)
RETURN SYS_REFCURSOR
IS
  TYPE T_REC IS RECORD
  (EMPLOYEE_ID    EMPLOYEES.EMPLOYEE_ID%TYPE,
   FIRST_NAME     EMPLOYEES.FIRST_NAME%TYPE,
   LAST_NAME      EMPLOYEES.LAST_NAME%TYPE,
   SALARY         EMPLOYEES.SALARY%TYPE,
   JOB_ID         EMPLOYEES.JOB_ID%TYPE
  );
  --
  TYPE T_REFTAB IS TABLE OF T_REC INDEX BY PLS_INTEGER;
  V_REFTAB T_REFTAB;
  --
  V_CURSOR SYS_REFCURSOR;
BEGIN
  OPEN V_CURSOR FOR
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         SALARY,
         JOB_ID
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = P_DEPTNO;
  --
  RETURN V_CURSOR;
  CLOSE V_CURSOR;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;
/
DECLARE
  V_EMPLOYEE_ID    EMPLOYEES.EMPLOYEE_ID%TYPE := NULL;
  V_FIRST_NAME     EMPLOYEES.FIRST_NAME%TYPE := NULL;
  V_LAST_NAME      EMPLOYEES.LAST_NAME%TYPE := NULL;
  V_SALARY         EMPLOYEES.SALARY%TYPE := NULL;
  V_JOB_ID         EMPLOYEES.JOB_ID%TYPE := NULL;
  --
  V_CUR SYS_REFCURSOR;
BEGIN
  V_CUR := FUN_REF_CUR_TEST(30);
  LOOP
    FETCH V_CUR INTO V_EMPLOYEE_ID,
                     V_FIRST_NAME,
                     V_LAST_NAME,
                     V_SALARY,
                     V_JOB_ID;
    --
    EXIT WHEN V_CUR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE -> ID -> '||V_EMPLOYEE_ID||' -> NAME -> '||V_FIRST_NAME||' '||V_LAST_NAME||' - > SALARY -> '||V_SALARY||' -> JOB_ID ->'||V_JOB_ID);
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR - > '||SQLERRM);
END;
/
How to fetch data from ref cursor in Oracle using for loop? : Output

RELATED TOPICS:

Author

Leave a Comment

Your email address will not be published.