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 |
| INVALID CURSOR |
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.
BEGIN
Execution section
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line CA
SELECT...INTO did not return any row.');
END:
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:
DECLARE
exception_name EXCEPTION;
PRAGMA
EXCEPTION INIT (exception_name,
Err_code);
BEGIN
Execution section
EXCEPTION
WHEN exception name THEN
handle the exception
END;
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.
