PL/SQL - Types of Exception: Named, Unnamed, user-defined Exception

Types of Exception PL/SQL

There are three types of Exceptions:

Types of Exception

Named System Exceptions

Unnamed System Exceptions

User-defined Exceptions

1) Named System Exceptions: System exceptions are automatically raised by Oracle, when a program violates a RDBMS rule. There some system exceptions which are raised frequently, so they are defined and given a name in Oracle which are known as Named Exceptions.

For example, NO_DATA_FOUND and ZERO_DIVIDE are called Named System exceptions.

Named system exceptions are:

i) Not Declared explicitly,

ii) Raised implicitly when a predefined Oracle error occurs,

iii) Caught by referencing the standard name within an exception-handling routine.

Exception Name Reason Error Number
CURSOR_ALREADY_OPEN When you open a cursor that is already open. ORA-06511

When you perform an invalid operation on a cursor like closing a cursor, fetch data from a cursor that is not opened. ORA-01001
NO_DATA_FOUND When a SELECT...INTO clause does not return any row from a table. ORA-01403
TOO_MANY_ROWS When you SELECT or fetch more than one row into a record or variable. ORA-01422
ZERO_DIVIDE When you attempt to divide a number by zero. ORA-01476

For example, suppose a NO_DATA_FOUND exception is raised in a proc, one can write a code to handle the exception as given below.


Execution section



dbms_output.put_line CA SELECT...INTO did not return any row.');


2) Unnamed System Exceptions: Those system exception for which oracle does not provide a name is known as unnamed system These exceptions do not occur frequently. These Exceptions code and an associated message.

There are two ways to handle unnamed system exceptions:

i) By using the WHEN OTHERS exception handler, or

ii) By associating the exception code to a name and using it as a named exception.

One can assign a name to unnamed system exceptions using a Pragma called EXCEPTION_INIT. EXCEPTION_INIT will associate a predefined Oracle error number to a programmer_defined exception name.

Steps to be followed to use unnamed system exceptions are:

i) They are raised implicitly

ii) If they are not handled in WHEN Others they must be handled explicitly,

iii) To handle the exception explicity, they must be declared using Pragma EXCEPTIONINIT as given above and handled referencing the user-defined exception name in the exception section.

The general syntax to declare unnamed system exception using EXCEPTION_INIT is:


exception_name EXCEPTION;


EXCEPTION INIT (exception_name, Err_code);


Execution section


WHEN exception name THEN

handle the exception



3) User-Defined Exceptions: Apart from system exceptions one can explicitly define exceptions based on business rules. These are as user-defined exceptions.

Steps to be followed to use user-defined exceptions:

i) They should be explicitly declared in the declaration section,

ii) They should be explicitly raised in the Execution Section,

iii) They should be handled by referencing the user-defined exception name in the exception section.