OracleConnections

Oracle Community Network

Handle ORACLE Exceptions

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;

2 members like this

▶ Reply to This

Replies to This Discussion

Permalink Reply by Freya Georgia on May 7, 2013 at 11:01am

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;

 /