Types of Exceptions

There are 3 types of Exceptions :

a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions

Two ways to handle unnamed system exceptions:

1. By using the WHEN OTHERS exception handler, or
2. By associating the exception code to a name and using it as a named exception.

Using User defined exceptions : 

RAISE_APPLICATION_ERROR is used for User defined exceptions in application.

1. to replace generic Oracle exception messages with our own, more meaningful messages.
2. The second is to create exception conditions of our own(range is in between -20000 and -20999)

To handle the exception explicitly, they must be declared using Pragma EXCEPTION_INIT

EXCEPTION_INIT will associate a predefined Oracle error number to a programmer_defined exception name.

To handle the exception explicitly, they must be declared using Pragma EXCEPTION_INIT as given below and handled referencing the user-defined exception name in the exception section.

DECLARE

   exception_name EXCEPTION;

   PRAGMA EXCEPTION_INIT (exception_name, Err_code);

BEGIN

Execution section

EXCEPTION

  WHEN exception_name THEN

     handle the exception

END;


Eg :

create or replace procedure new_emp
    ( p_name in emp.ename%type
      , p_sal in emp.sal%type
      , p_job in emp.job%type
      , p_dept in emp.deptno%type
      , p_mgr in emp.mgr%type 
      , p_hired in emp.hiredate%type := sysdate )
is
    invalid_manager exception;
    PRAGMA EXCEPTION_INIT(invalid_manager, -2291);
    dummy varchar2(1);
begin
    -- check hiredate is valid
    if trunc(p_hired) > trunc(sysdate) 
    then
        raise_application_error
            (-20000
             , 'NEW_EMP::hiredate cannot be in the future'); 
    end if;

    insert into emp
        ( ename
          , sal
          , job
          , deptno
          , mgr 
          , hiredate )
    values      
        ( p_name
          , p_sal
          , p_job
          , p_dept
          , p_mgr 
          , trunc(p_hired) );
exception
    when dup_val_on_index then
        raise_application_error
            (-20001
             , 'NEW_EMP::employee called '||p_name||' already exists'
             , true); 
    when invalid_manager then
        raise_application_error
            (-20002
             , 'NEW_EMP::'||p_mgr ||' is not a valid manager'); 

end;
/