An exception is a runtime error or warning condition, which can be predefined or user-defined. Predefined exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements
An exception declaration can appear only in the declarative part of a block, subprogram, or package. The scope rules for exceptions and variables are the same. But, unlike variables, exceptions cannot be passed as parameters to subprograms.
Predefined Exceptions
Some exceptions are predefined by PL/SQL. For a list of these exceptions, see "Predefined PL/SQL Exceptions". PL/SQL declares predefined exceptions globally in package STANDARD, so you need not declare them yourself.
Redeclaring predefined exceptions is error prone because your local declaration overrides the global declaration. In such cases, you must use dot notation to specify the predefined exception, as follows:
EXCEPTION
WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN ...
The exception-handling part of a PL/SQL block is optional. Exception handlers must come at the end of the block.
User-defined Exceptions
A User-defined exception has to be defined by the programmer. User-defined exceptions are declared in the declaration section with their type as exception. They must be raised explicitly using RAISE statement, unlike pre-defined exceptions that are raised implicitly. RAISE statement can also be used to raise internal exceptions.
Declaring Exception:
DECLARE
my_exception EXCEPTION;
BEGIN
------
Raising Exception:
BEGIN
RAISE my_exception;
-------
Handling Exception:
BEGIN
------
----
EXCEPTION
WHEN my_exception THEN
Statements;
END;
