处理ORACLE异常 [英] Handle ORACLE Exceptions

问题描述

我需要处理 ORA-01400错误（无法插入NULL使用异常句柄into（SCHEMATABLE_NAMECOLUMN_NAME））

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

ORACLE预定义一些例外（ACCESS_INTO_NULL，ZERO_DIVIDE等）但显然没有定义ORA-01400错误的异常，我该如何处理这个特定的错误代码？

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;

推荐答案

预定义的PL / SQL异常是>特殊到Oracle。你真的不能混淆那些。当你想要有一套你自己的预定义的例外，你不能象标准的那样声明他们全球。相反，请创建一个具有所有异常声明并在您的应用程序代码中使用它的例外包。

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.

例如：

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;
/

资料来源： http://www.orafaq.com/wiki/Exception

这篇关于处理ORACLE异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！