Trapping Non-Predefined oracle server errors

Trapping Non-Predefined oracle server errors:

• We can associate a named exception with a particular oracle error.
• The Non-predefined oracle server error is trapped by declaring it first or by using the OTHERS exception handle.
• The declare EXCEPTION is  RAISED implicitly by the oracle server.
• The PL/SQL PRAGMA EXCEPTION_INIT() can be used for associating EXCEPTION name with an oracle error number.
• The PRAGMA EXCEPTION_INTI() tells the PL/SQL engine completely to associate an EXCEPTION name with an oracle error number.
• The PRAGMA EXCEPTION_INIT() allows programmer to refer to any internal EXCEPTION by the name and associate that to specific handles.
• Pragma is a directive of compiler which tells compiler to associate error no with user declared exception at compile time.

Steps:
         1.Declare Exception.
         2.Associate Exception with Oracle error No.
Using Pragma
Exception_init(exception_name,oracle_error_number);
         3.Handle the raised exception.

• Exeption_name is the name of an exception declare prior to the pragma.
• Oracle_error_number is the desied error code to be associate with this named exception.

SQL>DECLARE
                  Pk_vio EXCEPTION;
                  PRAGMA EXCEPTION_INIT(pk_vio,-00001);
         BEGIN
                  Insert into emp(empno,ename,sal,deptno)
                  values(&eno,’&ename’,’&sal,&deptno);
         EXCEPTION
                  when pk_vio then
                  dbms_output.put_line(‘Duplicate empno is not allowed here’);
         END;