处理ORACLE异常

发布于 2024-08-16 10:37:31 字数 621 浏览 3 评论 0原文

我需要处理 ORA-01400 错误（无法将 NULL 插入 ("SCHEMA "."TABLE_NAME"."COLUMN_NAME") ) 使用异常句柄。

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

我需要这样的东西（接受其他建议）。

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

原文

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;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

半世晨晓 2024-08-23 10:37:31

预定义的 PL/SQL 异常对于 Oracle 来说是特殊的。你真的不能惹这些。当您想要拥有一组自己的预定义异常时，您无法像标准异常一样“全局”声明它们。相反，创建一个包含所有异常声明的异常包，并在您的应用程序代码中使用它。

示例：

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

现在使用包中定义的异常源

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

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

回复收藏 0 原文

梦在夏天 2024-08-23 10:37:31

您可以定义自己的异常，例如变量（它们与其他变量具有相同的范围，因此您可以定义包异常等...）：

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

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

回复收藏 0 原文

雨落□心尘 2024-08-23 10:37:31

您可以通过其代码来处理异常，如下所示：

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

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;

回复收藏 0 原文

给妤﹃绝世温柔 2024-08-23 10:37:31

 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 显示 sql 错误消息

http://www.psoug.org/reference/exception_handling.html< /a>

 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_handling.html

回复收藏 0 原文

~没有更多了~