Handle ORACLE Exceptions

oracle exception-handling plsql ora-01400
RRUZ picture RRUZ · Dec 29, 2009 · Viewed 16.3k times · Source

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

Adam Hawkes picture Adam Hawkes · Dec 29, 2009

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

Related questions

Oracle PL/SQL: how to get the stack trace, package name and procedure name

Sometimes the exception returns something like: "ORA-06502: PL/SQL: numeric or value error: character string buffer too small". It's not so readable since it doesn't report neither the table, the column and the value it tried to write. it would …

Read More
PL/SQL exception handling: do nothing (ignore exception)

This is a question I am asked very frequently. Since I couldn't find any exact duplicate on stackoverflow, I thought I'd post it as a reference. Question: In PL/SQL, I know how to catch exceptions and execute code when …

Read More
Java - find the first cause of an exception

I need to check if an exception is caused by some database problem. I receive an Exception and check if its cause contains the "ORA" string and return that (something like "ORA-00001"). The problem here is that the exception I …

Read More