有没有办法在约束违反时给出用户友好的错误消息 [英] Is there way to give user-friendly error message on constraint violation

问题描述

说我有列Gender和约束CHECK( Gender IN ('F', 'M', 'OTHER')).
如果我不小心忘记了在客户端处理此问题，则用户会看到类似
ORA-02290: check constraint (SYS_C099871244) violated
这对用户或维护或调试

Say I have column Gender and constraint CHECK( Gender IN ('F', 'M', 'OTHER')).
If I accidentally forgot to handle this on client side, user will see smth like
ORA-02290: check constraint (SYS_C099871244) violated
which is not very helpful nor for user, nor for developer who maintains or debug

有没有办法提供开发人员定义的消息，例如(伪)Java的
assert Gender IN (0,1):'Gender must be F or M'

Is there way to provide developer-defined message like (pseudo)Java's
assert Gender IN (0,1):'Gender must be F or M'

我能想到的唯一方法是将约束移到UPDATE或INSERT触发器之前，并在失败时执行Raise_Application_Error( code, my_message ).但我不喜欢

The only way I can think of is to move constraints to the BEFORE UPDATE OR INSERT trigger and on failure do Raise_Application_Error( code, my_message ). But I don't like it

编辑 具体原因列表，如评论中所述
1.我真的很想保持逻辑尽可能接近数据
2.对于最终用户，Raise_Application_Error消息与应用程序消息没有区别
3.即使绕过应用程序访问数据，开发人员也会看到不错的消息
4.将约束移至触发器很难看(是吗?)，所以我必须找到不同于Raise_Application_Error

EDIT List of specific reasons, as per in the comments
1. I really like to keep logic as close to data as possible
2. For end user Raise_Application_Error message is indistinguishable from application message
3. Developers will see nice message, even if access data bypassing application
4. moving constraints to triggers is ugly (is it?), so I have to find smth different from Raise_Application_Error

EDIT2 1.5年后的今天，我离开了与数据库相关的工作，终于想到了，我真的不喜欢这个-代码重复 .我必须在服务器端和客户端重复完全相同的逻辑.最有可能使用2种不同的语言.并使其保持同步.这太丑了.

EDIT2 1,5 years later, and after I've left db-related job, it has finally occured to me, what I really don't like about this - code duplication. I have to repeat exactly same logic on server and on client side. Most likely, in 2 different languages. And keep them in sync. This is just ugly.

尽管答案清楚地表明了这一点，但我对此无能为力.因此，现在是我当好公民，最后接受答案的时候了(对不起，忘了这个).

Though as answers clearly indicate, there is nothing I can about this. So it's time for me to be good citizen and finally accept an answer (sorry, just forgot about that).

推荐答案

如果您正在寻找一种方法来告诉Oracle始终将异常消息"ORA-02290:违反检查约束(SYS_C099871244)的异常消息"替换为另一条消息，例如"ORA-20001:性别必须为F或M"，那么答案是:不，不能这样做.

If you are looking for a way to tell Oracle always to replace the exception message "ORA-02290: check constraint (SYS_C099871244) violated" by another message like "ORA-20001: Gender must be F or M", then the answer is: no, it can't be done.

您可以做的是提供一个解决方案，供开发人员在其代码中使用，如下所示:

What you can do is provide a solution that can be used by developers in their code something like this:

...
begin
    insert into emp (empno, gender) values (p_empno, p_gender);
exception
    when others then
       error_pkg.handle_exception;
end;

error_pkg.handle_exception过程将解析Oracle异常消息并提取约束的名称(如果它是违反约束的)，然后在交叉引用表中查找该约束名称以获取所需的消息，然后使用raise_application_error用新消息重新引发异常.

The error_pkg.handle_exception procedure would parse the Oracle exception message and extract the name of the constraint (if it was a constraint violation) and lookup that constraint name in a cross reference table to get the required message, then use raise_application_error to re-raise the exception with the new message.

我想Oracle可以提供像这样的包和表作为标准，但是也许由于实际上在系统中对错误处理有很多不同的要求，因此认为它通常不够有用.

I suppose Oracle could offer a package and table like this as standard, but perhaps since in practice there are many different requirements for error handling in system, it isn't considered to be generally useful enough.

这篇关于有没有办法在约束违反时给出用户友好的错误消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！