Exception Handling

Predefined Exceptions :

•          NO_DATA_FOUND
•          CASE_NOT_FOUND
•          COLLECTION_IS_NULL
•          INVALID_CURSOR
•          LOGIN_DENIED
•          DUP_VAL_ON_INDEX
•          ROWTYPE_MISMATCH
•          TOO_MANY_ROWS
•          TIMEOUT_ON_RESOURCE
•          ZERO_DIVIDE

User Defined Exceptions :

Declaration :

                DECLARE
                                EXCEPTION_NAME EXCEPTION;

Scope:

                We cannot declare an exception twice in the same block. However, declare the same exception in different blocks.

Associating a PL/SQL  exception with an error number : PRAGMA EXCEPTION_INIT

                DECLARE
                                EXCEPTION_NAME EXCEPTION;
                                PRAGMA_EXCEPTION (EXCEPTION_NAME,-60);
                BEGIN
                                /* Code that causes ORA-00060 error*/

                EXCEPTION
                                WHEN EXCEPTION_NAME           THEN
                                /*  Handle the error  */

                END  ;                /

Defining Our Own Error Messages : Procedure RAISE_APPLICATION_ERROR

                It lets you define the user defined ORA - error messages from stored programs. In that way we can report errors to our application and avoid returning unhandled exceptions.

                RAISE_APPLICATION_ERROR(error_number,message[,TRUE/FALSE]);
                                where error_number is a negative integer between -20000 and -20999, and message is a character string up to 2048 bytes long.

                RAISE_APPLICATION_ERROR is the part of DBMS_STANDARD package