Navigate

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

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;
/

RELATED TOPICS:

Author

Related Post
  1. How to fetch multiple columns from cursor in Oracle?
  2. Oracle function return Array of Varchar2