Handle ORACLE Exceptions

The pre-defined PL/SQL exceptions are special to Oracle. You really can't mess with those. When you want to have a set of predefined exceptions of your own you can't declare them "globally" like the standard ones. Instead, create an exceptions package which has all of the exception declarations and use that in your application code.

Example:

CREATE OR REPLACE PACKAGE my_exceptionsAS  insert_null_into_notnull EXCEPTION;  PRAGMA EXCEPTION_INIT(insert_null_into_notnull, -1400);  update_null_to_notnull EXCEPTION;  PRAGMA EXCEPTION_INIT(update_null_to_notnull, -1407);END my_exceptions;/

Now use the exception defined in the package

CREATE OR REPLACE PROCEDURE use_an_exception ASBEGIN  -- application specific code ...  NULL;EXCEPTION  WHEN my_exceptions.insert_null_into_notnull THEN     -- application specific handling for ORA-01400: cannot insert NULL into (%s)     RAISE;END;/

Source: http://www.orafaq.com/wiki/Exception

You can handle exception by its code like this:

.... ... INSERT INTO MY_TABLE (CODE, NAME) VALUES (aCode,aName);    COMMIT;      EXCEPTION        WHEN OTHERS THEN          IF SQLCODE = -1400 THEN         Do_MyStuff();       ELSE         raise_application_error(SQLCODE,MY_OWN_FORMAT_EXCEPTION(SQLCODE,SQLERRM),TRUE);       END IF;    END;   

you can define your own exceptions, like variables (they will have the same scope as other variables so you can define package exception, etc...):

SQL> DECLARE  2     NULL_VALUES EXCEPTION;  3     PRAGMA EXCEPTION_INIT(NULL_VALUES, -1400);  4  BEGIN  5     INSERT INTO t VALUES (NULL);  6  EXCEPTION  7     WHEN null_values THEN  8        dbms_output.put_line('null value not authorized');  9  END; 10  /null value not authorizedPL/SQL procedure successfully completed