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;

Read More

What is Exception Handling in PL/SQL?

 What is Exception Handling in PL/SQL?

An exception occurs when the PL/SQL engine encounters an instruction which it cannot execute due to an error that occurs at run-time. These errors will not be captured at the time of compilation and hence these needed to handle only at the run-time.

For example, if PL/SQL engine receives an instruction to divide any number by ‘0’, then the PL/SQL engine will throw it as an exception. The exception is only raised at the run-time by the PL/SQL engine.

Exceptions will stop the program from executing further, so to avoid such condition, they need to be captured and handled separately. This process is called as Exception-Handling, in which the programmer handles the exception that can occur at the run time.

Syntax

BEGIN

<execution block>

.

.

EXCEPTION

WHEN <exceptionl_name>

THEN

  <Exception handling code for the “exception 1 _name’' >

WHEN OTHERS

THEN

  <Default exception handling code for all exceptions >

END;

Types of Exception

There are two types of Exceptions in Pl/SQL.

Predefined Exceptions

User-defined Exception

Example 1:- Pre define Exception-:

DECLARE

  INTEGRITY_EXCEP EXCEPTION;

  PRAGMA EXCEPTION_INIT(INTEGRITY_EXCEP,-2292);

BEGIN

  DELETE FROM SANDEPT

  WHERE DEPARTMENT_ID=&DEPTNO;

EXCEPTION

  WHEN INTEGRITY_EXCEP THEN

    DBMS_OUTPUT.PUT_LINE('EMPLOYEES PRESENT IN THIS DEPARTMENT...');

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

Example 2:-

SET FEEDBACK ON

SET VERIFY OFF

SET SERVEROUT ON

CREATE OR REPLACE PROCEDURE SALPRO(P_ID IN NUMBER)

DECLARE

  V_NAME VARCHAR2(20);

is

BEGIN

  SELECT LAST_NAME

  INTO   V_NAME

  FROM   EMPLOYEES

  WHERE  DEPARTMENT_ID='&DEPTNO';

  DBMS_OUTPUT.PUT_LINE(V_NAME);

EXCEPTION

  WHEN TOO_MANY_ROWS THEN

      DBMS_OUTPUT.PUT_LINE('MULTIPLE EMPLOYEES IN THIS DEPARTMENT...');

  WHEN NO_DATA_FOUND THEN

      DBMS_OUTPUT.PUT_LINE('NO SUCH DEPARTMENT...');

  WHEN OTHERS THEN

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

END;

Read More