Exceptions : Exception « PL SQL Programming « Oracle PL/SQL Tutorial






In PL/SQL, the user can catch certain runtime errors.

Exceptions can be internally defined by Oracle or the user.

Exceptions are used to handle errors that occur in your PL/SQL code.

A PL/SQL block contains an EXCEPTION block to handle exception.

There are three types of exceptions:

  1. Predefined Oracle errors
  2. Undefined Oracle errors
  3. User-defined errors

The different parts of the exception.

  1. Declare the exception.
  2. Raise an exception.
  3. Handle the exception.

An exception has four attributes:

  1. Name provides a short description of the problem.
  2. Type identifies the area of the error.
  3. Exception Code gives a numeric representation of the exception.
  4. Error message provides additional information about the exception.

The predefined divide-by-zero exception has the following values for the attributes:

  1. Name = ZERO_DIVIDE
  2. Type = ORA (from the Oracle engine)
  3. Exception Code = C01476
  4. Error message = divisor is equal to zero

The following is the typical syntax of an exception-handling PL/SQL block.

exception

  when exception_1 THEN

  statements

  when exception_2 THEN

  statements

  ...

exception_1 and exception_2 are the names of the predefined exceptions.

statements is the PL/SQL code that will be executed if the exception name is satisfied.









24.14.Exception
24.14.1.Exceptions
24.14.2.Types of more commonly used Exceptions
24.14.3.Exception Handling
24.14.4.The NULL Statement
24.14.5.ZERO_DIVIDE Exception
24.14.6.When the exception occurs, program control passes to the EXCEPTION block where the WHEN clause is examined for a matching exception.
24.14.7.Understanding Different Exception Types
24.14.8.DUP_VAL_ON_INDEX Exception
24.14.9.INVALID_NUMBER Exception
24.14.10.Catch 'divide by zero' exception
24.14.11.OTHERS Exception
24.14.12.Because OTHERS handles all exceptions, you must list it after any specific exceptions in your EXCEPTION block.
24.14.13.Declare variables in exception handler
24.14.14.Data not found exception
24.14.15.Too many rows exception