Fetch into Record %ROWTYPEd to Explicit Cursor

View All Scripts Login to Run Script

Script Name Fetch into Record %ROWTYPEd to Explicit Cursor
Description If you are using an explicit cursor to fetch one or more rows, always fetch into a record that is declared based on that cursor, as in "my_rec my_cur%ROWTYPE;" That way, you avoid having to declare lots of individual variables; you don't have to remember and do all the typing to use %TYPE for each variable; when the cursor's SELECT list changes, you don't have to change the INTO list of the FETCH statement. Of course, you will still almost certainly need to change something else in your code to use (or stop using) the different elements in the SELECT list.
Area PL/SQL General
Contributor Steven Feuerstein
Created Friday September 02, 2016

Statement 1

In this first iteration, I have an explicit cursor that fetches three elements (two columns, one expression). So, well, why not declare an individual variable for each one of those elements? Lots of typing, no use of %TYPE to anchor those variables back to the elements fetched, and if/when the SELECT list changes, you've got to go declare another variable.

"Manual" Declaration of Variables to Match Cursor SELECT List

CREATE OR REPLACE PROCEDURE process_employee (  
   employee_id_in   IN NUMBER)  
IS  
   l_id       NUMBER;  
   l_salary   NUMBER (9, 2);  
   l_name     VARCHAR2 (100);  
  
   CURSOR emp_cur  
   IS  
      SELECT employee_id,  
             salary,  
             last_name || ',' || first_name  
        FROM hr.employees  
       WHERE employee_id = employee_id_in;  
BEGIN  
   OPEN emp_cur;  
  
   FETCH emp_cur INTO l_id, l_salary, l_name;  
  
   DBMS_OUTPUT.put_line (  
      l_id || '-' || l_name || '-' || l_salary);  
  
   CLOSE emp_cur;  
END;

Procedure created.

Statement 2

BEGIN 
   process_employee (100); 
END;

100-King,Steven-24000

Statement 3

This is a form of hard-coding: there is a dependency in the FETCH-INTO list on the SELECT list in the query. I change one (the cursor) but forget/neglect to change the other...compile error!

Add Column to SELECT List, Program Breaks

CREATE OR REPLACE PROCEDURE process_employee (  
   employee_id_in   IN NUMBER)  
IS  
   l_id           NUMBER;  
   l_salary       NUMBER (9, 2);  
   l_name         VARCHAR2 (100);  
   l_first_name   VARCHAR2 (100);  
  
   CURSOR emp_cur  
   IS  
      SELECT employee_id,  
             salary,  
             last_name || ',' || first_name lname,  
             first_name  
        FROM hr.employees  
       WHERE employee_id = employee_id_in;  
BEGIN  
   OPEN emp_cur;  
  
   FETCH emp_cur INTO l_id, l_salary, l_name;  
  
   DBMS_OUTPUT.put_line (  
      l_id || '-' || l_name || '-' || l_salary);  
  
   CLOSE emp_cur;  
END;

Errors: PROCEDURE PROCESS_EMPLOYEE
Line: 20 PLS-00394: wrong number of values in the INTO list of a FETCH statement
Line: 20 PL/SQL: SQL Statement ignored

More Details: https://docs.oracle.com/error-help/db/ora-24344

Statement 4

Now I get rid of my individual, "manual" declarations. Instead, I just have one smart declaration of a record tied directly to the cursor with %ROWTYPE. I also use %TYPE for my parameter.

Declare Record Based on Cursor

CREATE OR REPLACE PROCEDURE process_employee (  
   employee_id_in   IN hr.employees.employee_id%TYPE)  
IS  
   CURSOR emp_cur  
   IS  
      SELECT employee_id,  
             salary,  
             last_name || ',' || first_name full_name  
        FROM hr.employees  
       WHERE employee_id = employee_id_in;  
  
   emp_rec    emp_cur%ROWTYPE;  
BEGIN  
   OPEN emp_cur;  
  
   FETCH emp_cur INTO emp_rec;  
  
   DBMS_OUTPUT.put_line (  
         emp_rec.employee_id  
      || '-'  
      || emp_rec.full_name 
      || '-'  
      || emp_rec.salary);  
  
   CLOSE emp_cur;  
END;

Procedure created.

Statement 5

BEGIN 
   process_employee (100); 
END;

100-King,Steven-24000

Statement 6

But of course you will still need to make other changes as necessary to utilize that new value (in this case, the first_name all by itself) in your program.

Add Column to SELECT List, Program Still Works

CREATE OR REPLACE PROCEDURE process_employee (  
   employee_id_in   IN hr.employees.employee_id%TYPE)  
IS  
   CURSOR emp_cur  
   IS  
      SELECT employee_id,  
             salary,  
             last_name || ',' || first_name full_name,  
             first_name  
        FROM hr.employees  
       WHERE employee_id = employee_id_in;  
  
   emp_rec   emp_cur%ROWTYPE;  
BEGIN  
   OPEN emp_cur;  
  
   FETCH emp_cur INTO emp_rec;  
  
   DBMS_OUTPUT.put_line (  
         emp_rec.employee_id  
      || '-'  
      || emp_rec.full_name  
      || '-'  
      || emp_rec.salary);  
  
   CLOSE emp_cur;  
END;

Procedure created.

Statement 7

BEGIN 
   process_employee (100); 
END;

100-King,Steven-24000

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database