TOO_MANY_ROWS Exception in Oracle

 TOO_MANY_ROWS

ORA-01422

When you use SELECT in a PL/SQL block, it's important to make sure that exactly one row will always be returned by your query. ... If more than one row is returned, the TOO_MANY_ROWS exception occurs.

Single row SELECT returned more than one row.

Example:

create or replace procedure pro1(deptno in number)

DECLARE

  V_NAME VARCHAR2(20);

 is

BEGIN

  SELECT LAST_NAME

  INTO   V_NAME

  FROM   EMPLOYEES

  WHERE  EMPLOYEE_ID='&DEPTNO';

  DBMS_OUTPUT.PUT_LINE(V_NAME);

EXCEPTION

  -- 1 HANDLER HANDLING 2 EXCEPTIONS

-----------------------------------------------------------------

  WHEN TOO_MANY_ROWS OR NO_DATA_FOUND THEN

      DBMS_OUTPUT.PUT_LINE('NOT A VALID DEPARTMENT...');

  WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE('NOT A VALID NUMBER...');

END;

end pro;