处理ORACLE异常

本文介绍了处理ORACLE异常的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要处理 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; /

资料来源： www.orafaq/wiki/Exception