Handling TOO_MANY_ROWS Exception in PL/SQL

PL/SQL Exception Handling: Exercise-3 with Solution

Handle the TOO_MANY_ROWS exception when retrieving multiple rows instead of a single row from a table.

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
  v_dep_id employees.department_id%TYPE := 90;
  v_emp_id employees.employee_id%TYPE; 
  v_emp_name employees.first_name%TYPE;
  v_emp_salary employees.salary%TYPE;
  v_exception_msg VARCHAR2(200);
BEGIN
  SELECT first_name, department_id, salary
  INTO v_emp_name,v_dep_id, v_emp_salary
  FROM employees
  WHERE department_id = v_dep_id;
  EXCEPTION
    WHEN TOO_MANY_ROWS THEN
      v_exception_msg := 'Multiple rows found for the given department ID: ' || v_dep_id;
      DBMS_OUTPUT.PUT_LINE(v_exception_msg);
    WHEN OTHERS THEN
      v_exception_msg := 'An error occurred: ' || SQLERRM;
      DBMS_OUTPUT.PUT_LINE(v_exception_msg);
END;

Sample Output:

multiple rows found for the given department ID: 90

Explanation:

The said code in Oracle's PL/SQL demonstrates the handling of the TOO_MANY_ROWS exception when retrieving multiple rows based on the department_id from the 'employees' table.

The variable v_dep_id is assigned by the department ID value 90.

The SELECT INTO statement retrieves the first_name, department_id, and salary from the employees table based on the specified department_id value.

The query returns multiple rows, and the TOO_MANY_ROWS exception is raised, and the corresponding exception block is triggered and the variable v_exception_msg is assigned an error message indicating that multiple rows were found for the given department ID. This message is then displays using DBMS_OUTPUT.PUT_LINE.

The OTHERS exception block is used to handle any other exceptions that may occur during the execution of the code. This message is also printed using DBMS_OUTPUT.PUT_LINE.

Flowchart:

Previous: Handling NO_DATA_FOUND exception in PL/SQL.
Next: Handling invalid number exception in PL/SQL.