Exception Handling

PL/SQL Exception message consists of three parts.
1) Type of Exception
2) An Error Code
3) A message

3) Types of Exception.
There are 3 types of Exceptions.
a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions

a) Named System Exceptions :Like NO_DATA_FOUND ZERO_DIVIDE,CURSOR_ALREADY_OPEN,INVALID_CURSOR,TOO_MANY_ROWS.
These are not declared explicitly, raised implicitly when a predefined Oracle error occurs.
e.g:

BEGIN 

select * from per_all_people_f where employee_number=100;

EXCEPTION 

WHEN NO_DATA_FOUND THEN 

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

END; 

b) Unnamed System Exceptions:These exception are user defined once.We can assosiate this to oracle error code using a pragma statement EXECEPTION_INIT
e.g:
Declare
  exception_name EXCEP_1;

PRAGMA 

EXCEPTION_INIT ( EXCEP_1,-2292); /* The erroe code range from range from -20000 to -20999 are user-defined*/

BEGIN
select * from per_all_people_f where employee_number=100;
EXCEPTION
WHEN EXCEP_1 THEN
dbms_output.put_line ('A SELECT...INTO did not return any row.');
END;
c) User-defined Exceptions:this is the condition where the user need to check for occurance of a exception while validating large number of data.Need to define and Raise the exception explicitly.
e.g:
Declare
exception_name EXCEP_1;
BEGIN
select * from per_all_people_f where employee_number=100;
Raise EXCEP_1;
exception
WHEN EXCEP_1 THEN
dbms_output.put_line ('A SELECT...INTO did not return any row.');
END;

RAISE_APPLICATION_ERROR ( ) :

We can relate the user defined exception to the predefine errcode of oracle using raise_application_error.
the advabtage if using this is all previous transactions which are not committed within the PL/SQL Block are rolled back automatically.And to make to make the user-defined exception look like an Oracle error.

Syntax:
RAISE_APPLICATION_ERROR (error_number, error_message);

/* The Error number must be between -20000 and -20999 */