How to fetch multiple columns from cursor in Oracle?

How to fetch multiple columns from cursor in Oracle?

In this article we will see how to fetch multiple columns and rows from a cursor.

We will check 3 ways to fetch multiple columns from a cursor:

• Using simple PLSQL variable
• Using PLSQL Record Type
• Fetching multiple columns using collections
  • Using Table of Record Type
  • Using Table of Individual Column Type

We will use the same example through out the article. Then we will check the code level differences.

Using Simple PLSQL Variables to fetch multiple columns from Cursor:

--How to fetch multiple columns from cursor in Oracle? : Example

SET SERVEROUTPUT ON;
DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID,
         SALARY
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  --
  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_JOB_ID      EMPLOYEES.JOB_ID%TYPE := NULL;
  V_SALARY      EMPLOYEES.SALARY%TYPE := NULL;
  --
BEGIN
  OPEN EMP_CUR;
  LOOP
    FETCH EMP_CUR INTO V_EMPLOYEE_ID,
                       V_FIRST_NAME,
                       V_LAST_NAME,
                       V_JOB_ID,
                       V_SALARY;
    EXIT WHEN EMP_CUR%NOTFOUND;
    --
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE DETAILS -> ID -> '||V_EMPLOYEE_ID||' -> NAME ->'||V_FIRST_NAME||' '||V_LAST_NAME||' -> JOB_ID -> '||V_JOB_ID||' -> SALARY -> '||V_SALARY);
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/

Using PLSQL records:

--How to fetch multiple columns from cursor in Oracle? : Example

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID,
         SALARY
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  --
  TYPE EMP_REC IS RECORD
  (
   EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE,
   FIRST_NAME  EMPLOYEES.FIRST_NAME%TYPE,
   LAST_NAME   EMPLOYEES.LAST_NAME%TYPE,
   JOB_ID      EMPLOYEES.JOB_ID%TYPE,
   SALARY      EMPLOYEES.SALARY%TYPE
  );
  --
  V_EMP_REC EMP_REC;
BEGIN
  OPEN EMP_CUR;
  LOOP
    FETCH EMP_CUR INTO V_EMP_REC;
    EXIT WHEN EMP_CUR%NOTFOUND;
    --
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE DETAILS -> ID -> '||V_EMP_REC.EMPLOYEE_ID||' -> NAME ->'||V_EMP_REC.FIRST_NAME||' '||V_EMP_REC.LAST_NAME||' -> JOB_ID -> '||V_EMP_REC.JOB_ID||' -> SALARY -> '||V_EMP_REC.SALARY);
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/

Fetching multiple columns using Collections:

Table of Record Type:

--How to fetch multiple columns from cursor in Oracle? : Example

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID,
         SALARY
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  --
  TYPE EMP_REC IS RECORD
  (
   EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE,
   FIRST_NAME  EMPLOYEES.FIRST_NAME%TYPE,
   LAST_NAME   EMPLOYEES.LAST_NAME%TYPE,
   JOB_ID      EMPLOYEES.JOB_ID%TYPE,
   SALARY      EMPLOYEES.SALARY%TYPE
  );
  --
  TYPE T_EMP_TAB IS TABLE OF EMP_REC;
  V_EMP_TAB T_EMP_TAB;
BEGIN
  OPEN EMP_CUR;
  FETCH EMP_CUR BULK COLLECT INTO V_EMP_TAB;
  --
  FOR I IN 1..V_EMP_TAB.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE DETAILS -> ID -> '||V_EMP_TAB(I).EMPLOYEE_ID||' -> NAME ->'||V_EMP_TAB(I).FIRST_NAME||' '||V_EMP_TAB(I).LAST_NAME||' -> JOB_ID -> '||V_EMP_TAB(I).JOB_ID||' -> SALARY -> '||V_EMP_TAB(I).SALARY);
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/

Table of Individual Column Type:

--How to fetch multiple columns from cursor in Oracle? : Example

DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         JOB_ID,
         SALARY
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = 30;
  --
  TYPE T_EMPLOYEE_ID IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE INDEX BY PLS_INTEGER;
  V_EMPLOYEE_ID T_EMPLOYEE_ID;
  TYPE T_FIRST_NAME IS TABLE OF EMPLOYEES.FIRST_NAME%TYPE INDEX BY PLS_INTEGER;
  V_FIRST_NAME T_FIRST_NAME;
  TYPE T_LAST_NAME IS TABLE OF EMPLOYEES.LAST_NAME%TYPE INDEX BY PLS_INTEGER;
  V_LAST_NAME T_LAST_NAME;
  TYPE T_JOB_ID IS TABLE OF EMPLOYEES.JOB_ID%TYPE INDEX BY PLS_INTEGER;
  V_JOB_ID T_JOB_ID;
  TYPE T_SALARY IS TABLE OF EMPLOYEES.SALARY%TYPE INDEX BY PLS_INTEGER;
  V_SALARY T_SALARY;
  --
BEGIN
  OPEN EMP_CUR;
  FETCH EMP_CUR BULK COLLECT INTO V_EMPLOYEE_ID,
                                  V_FIRST_NAME,
                                  V_LAST_NAME,
                                  V_JOB_ID,
                                  V_SALARY;
  --
  FOR I IN 1..V_EMPLOYEE_ID.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE DETAILS -> ID -> '||V_EMPLOYEE_ID(I)||' -> NAME ->'||V_FIRST_NAME(I)||' '||V_LAST_NAME(I)||' -> JOB_ID -> '||V_JOB_ID(I)||' -> SALARY -> '||V_SALARY(I));
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR -> '||SQLERRM);
END;
/

RELATED TOPICS:

• Working With Cursors
• How to fetch data from ref cursor in Oracle using for loop?
• USING BULK OPERATION IN PLSQL
• USING BULK BINDING IN ORACLE