Handle ORACLE Exceptions

Question

I need to handle the ORA-01400 error (cannot insert NULL into ("SCHEMA"."TABLE_NAME"."COLUMN_NAME") ) using a exception handle.

ORACLE Predefine a few Exceptions like (ACCESS_INTO_NULL, ZERO_DIVIDE and so on), but apparently does not define an Exception for the ORA-01400 error, how do I handle this particular error code?

I need something like this (other suggestions are accepted).

....
 ...     
 INSERT INTO MY_TABLE (CODE, NAME) VALUES (aCode,aName);
 COMMIT;
   EXCEPTION
     WHEN NULL_VALUES THEN /* i don't know this value , exist?*/
       Do_MyStuff();
     WHEN OTHERS THEN
       raise_application_error(SQLCODE,MY_OWN_FORMAT_EXCEPTION(SQLCODE,SQLERRM),TRUE); 
    END;

Answer 1

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_exceptions
AS
  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 AS
BEGIN
  -- 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