Handling ROWTYPE_MISMATCH exception in PL/SQL

PL/SQL Exception Handling: Exercise-14 with Solution

Handle the ROWTYPE_MISMATCH exception when assigning values to variables of incompatible row types.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

DECLARE
  TYPE v_emp IS REF CURSOR RETURN employees%ROWTYPE;
v_emp_refv_emp;
emp_recemployees%ROWTYPE;
  PROCEDURE Return_Dept_Row(v_emp IN OUT SYS_REFCURSOR)
  IS
  BEGIN
    OPEN v_emp FOR SELECT * FROM departments;
  END Return_Dept_Row;
BEGIN
Return_Dept_Row(v_emp_ref);
  LOOP
    FETCH v_emp_ref INTO emp_rec;
    EXIT WHEN v_emp_ref%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(emp_rec.employee_id || '--' || emp_rec.first_name || '--' || emp_rec.last_name || '--' || emp_rec.job_id);
  END LOOP;
EXCEPTION
  WHEN ROWTYPE_MISMATCH THEN
    DBMS_OUTPUT.PUT_LINE('Reason for Error --> ' || SQLERRM);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Reason for Error --> ' || SQLERRM);
END;
/

Sample Output:

Reason for Error --> ORA-06504: PL/SQL: Return types of Result Set variables or query do not match

Explanation:

The said code in Oracle's PL/SQL that demonstrates how to handle the ROWTYPE_MISMATCH exception that may occur when fetching data into a record variable from a cursor, particularly when the structure of the fetched row does not match the structure of the record variable.

The REF CURSOR v_emp of type employees ROWTYPE and a cursor variable v_emp_ref of type v_emp and a record variable emp_rec of type employees ROWTYPE are declared.

A procedure named Return_Dept_Row is defines that takes an IN OUT parameter v_emp of type SYS_REFCURSOR and it opens the v_emp cursor for the SELECT statement that retrieves all rows from the departments table.

In the main block, a procedure Return_Dept_Row is called with the v_emp_ref cursor variable as the argument and it populates the v_emp_ref cursor variable with the departments table rows.

The code then fetches rows from the v_emp_ref cursor variable into the emp_rec record variable using a loop that continues until there are no more rows to fetch.

When the ROWTYPE_MISMATCH exception occurs during the fetch operation, the exception block catches it and displays an appropriate error message using DBMS_OUTPUT.PUT_LINE. The OTHERS handler is used to catch any other exceptions and display their error message.

Flowchart:

Previous: Handling SUBSCRIPT_BEYOND_COUNT exception in PL/SQL.
Next: Handling ACCESS_INTO_NULL exception in PL/SQL.