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

Hi RRUZ,

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 authorized

PL/SQL procedure successfully completed

Answer 2

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;   

Answer 3

 INSERT INTO MY_TABLE (CODE, NAME) VALUES (aCode,aName);
 COMMIT;  

      EXCEPTION
         WHEN NULL_VALUES /* i don't know this value , exist?*/
           emesg := SQLERRM;
           dbms_output.put_line(emesg); 
         WHEN OTHERS THEN
           emesg := SQLERRM;
           dbms_output.put_line(emesg);
         END;

---

SQLERRM shows the sql error message

http://www.psoug.org/reference/exception%5Fhandling.html

Answer 4

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