User-defined exceptions in PL/SQL

If you are required to standardize the exception handling, not just to control the abnormal program flow but also to alter the program execution logic, you need to use user-defined exceptions. The user-defined exceptions are raised in the BEGIN..END section of the block using the RAISE statement.

With custom exceptions, Oracle allows users to specify names, associate error codes, and raise statements in line with the implementation logic.

There are three ways to declare user-defined exceptions.

• EXCEPTION type variable:

  • Declare the EXCEPTION type variable in the declaration section.
  • Raise it explicitly in the program body using the RAISE statement.
  • Handle it in the EXCEPTION section. No error code will be involved here.

• EXCEPTION variable:

  Declare the EXCEPTION variable and associate it with a standard error number using PRAGMA EXCEPTION_INIT.

• RAISE_APPLICATION_ERROR:

  Use the RAISE_APPLICATION_ERROR to declare a dedicated error number and error message.

Related Post(s):

• Exception handling in PL/SQL