oracle 有没有办法在违反约束时给出用户友好的错误消息

Is there way to give user-friendly error message on constraint violation

提问by Alexander Malakhov

Say I have column Genderand 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

假设我有 columnGender和 constraint CHECK( Gender IN ('F', 'M', 'OTHER'))。
如果我不小心忘记在客户端处理这个，用户会看到类似的东西
ORA-02290: check constraint (SYS_C099871244) violated
，这对用户和维护或调试的开发人员都不是很有帮助

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

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

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

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

EDITList 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

编辑特定原因列表，如评论中所述
1. 我真的很喜欢使逻辑尽可能接近数据
2. 对于最终用户 Raise_Application_Error 消息与应用程序消息无法区分
3. 开发人员会看到很好的消息，即使访问数据绕过应用程序
4. 将约束移动到触发器是丑陋的（是吗？），所以我必须找到与 Raise_Application_Error 不同的地方

EDIT21,5 years later, and after I've left db-related job, it has finally occured to me, what I reallydon'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.

EDIT21,5 年后，在我离开与数据库相关的工作后，我终于意识到，我真的不喜欢这个 -代码重复。我必须在服务器端和客户端重复完全相同的逻辑。最有可能的是 2 种不同的语言。并使它们保持同步。这太丑了。

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

尽管正如答案清楚地表明的那样，但我对此无能为力。所以现在是我成为好公民并最终接受答案的时候了（对不起，只是忘记了）。

采纳答案by Tony Andrews

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.

如果您正在寻找一种方法来告诉 Oracle 始终将异常消息“ORA-02290：检查约束（SYS_C099871244）违反”替换为“ORA-20001：性别必须为 F 或 M”之类的另一条消息，那么答案是：不，这是做不到的。

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;

The error_pkg.handle_exceptionprocedure 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_errorto re-raise the exception with the new message.

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

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.

我想 Oracle 可以提供这样的包和表作为标准，但也许因为在实践中系统中的错误处理有许多不同的要求，所以通常认为它不够有用。

回答by paxdiablo

Constraints are what databases use to protect themselves from errant applications,not from users.

约束是数据库用来保护自己免受错误应用程序，而不是用户的侵害。

That means that constraint violations should be captured by the application and possiblycleaned up for presentation to the user. I'd consider an application which didn't do that to be deficient in some manner.

这意味着应用程序应该捕获违反约束的情况，并可能清除以呈现给用户。我认为没有这样做的应用程序在某些方面存在缺陷。

I say 'possibly' since your application (at least for this case) should never see that happen. It should almost certainly be using a drop down limited-choice control for something like that. If it used a combo-box or (shock, horror) a free-format text entry field, it would need to be redefined.

我说“可能”是因为您的应用程序（至少在这种情况下）永远不会看到这种情况发生。几乎可以肯定，它应该使用下拉式有限选择控件来实现类似的功能。如果它使用组合框或（震惊、恐怖）自由格式的文本输入字段，则需要重新定义。

That would mean that the violation would never occur unless, of course, the application and the constraint get out of sync at some point. But that's something that should be caught in testing, long before a customer ever gets their grubby little hands on your application.

这意味着除非应用程序和约束在某个时刻不同步，否则永远不会发生违规。但是，在客户接触您的应用程序之前很久，就应该在测试中发现这一点。

---

---

To answer your actual question, the messages that come out of Oracle for constraint violations cannot be changed. The best you can do is to name your constraints intelligently so that it may make sense to an end user.

为了回答您的实际问题，无法更改从 Oracle 发出的违反约束的消息。您能做的最好的事情是智能地命名您的约束，以便它对最终用户有意义。

But I still maintain that this presentation of problems to a user is a responsibility of the application layer, not the database layer.

但我仍然认为，将问题呈现给用户是应用层的责任，而不是数据库层。

回答by Unreason

In short:
No way of catching oracle errors for custom handling that I know of. However I don't think you should be trying to do that anyway.

简而言之：
无法捕获我所知道的自定义处理的 oracle 错误。但是，我认为无论如何您都不应该尝试这样做。

---

---

Long version:
The intentions behind your reasons are good, however...

长版：
你的理由背后的意图是好的，但是......

I really like to keep logic as close to data as possible

我真的很喜欢让逻辑尽可能接近数据

Logic should be as close to data as possible, that is true; however this does not qualify - this is not logic, this is presentationof codes that identify exceptions to already defined logic, and presentation should not be mixed with data or logic layers (the domain of error messages spans over every part of the system; from client side to server side, also think about translating, consistent updates, easier management and overview of the messages, etc...)

逻辑应该尽可能接近数据，这是真的；但是这不符合条件 - 这不是逻辑，这是识别已定义逻辑异常的代码的呈现，并且呈现不应与数据或逻辑层混合（错误消息的域跨越系统的每个部分；从客户端到服务器端，还要考虑翻译、一致更新、更容易的消息管理和概览等...）

For end user Raise_Application_Error message is indistinguishable from application message

对于最终用户 Raise_Application_Error 消息与应用程序消息无法区分

True, but the reverse is valid as well and therefore not particularly relevant - if you have central repository of DB error codes, application error codes, and error handling will process it then it is irrelevant (for end user) which layer is presenting error messages. Also, long term, it is not clear that it would save you any work.

是的，但反过来也是有效的，因此不是特别相关 - 如果您有 DB 错误代码、应用程序错误代码的中央存储库，并且错误处理将处理它，那么（对于最终用户）哪个层显示错误消息是无关紧要的. 此外，从长远来看，尚不清楚它是否可以为您节省任何工作。

Developers will see nice message, even if access data bypassing application

即使绕过应用程序访问数据，开发人员也会看到很好的消息

This is true, for developers accessing DB directly there would be nicer error messages. Still a few comments apply here - in complex systems bypassing the application layer should not be allowed (even for developers); if that would be allowed you would expect devs to know where to look up the error messages from the constraint names (central repository of error codes and messages should/would be maintained in the same db)

这是真的，对于直接访问 DB 的开发人员来说，会有更好的错误消息。还有一些评论适用于此 - 在复杂的系统中不应允许绕过应用程序层（即使对于开发人员）；如果允许，您希望开发人员知道从约束名称中查找错误消息的位置（错误代码和消息的中央存储库应该/将在同一个数据库中维护）

moving constraints to triggers is ugly (is it?), so I have to find smth different from Raise_Application_Error

将约束移动到触发器是丑陋的（是吗？），所以我必须找到与 Raise_Application_Error 不同的地方

It is ugly in a sense that it is presentation and should not be in DDL. Also, it incurs unjustified(?) performance penalties if done through triggers (not sure how big, nor how elegant it could be done).

从某种意义上说，它是演示文稿，不应该在 DDL 中，这是丑陋的。此外，如果通过触发器完成，它会导致不合理的（？）性能损失（不确定它有多大，也不知道它可以做到多优雅）。

Note: All in all I do agree that it would be a nice feature to have possibility to hook into DBMS error handling.

注意：总而言之，我确实同意有可能挂钩 DBMS 错误处理将是一个很好的功能。

However, error handling and error message processing has following properties

但是，错误处理和错误消息处理具有以下属性

• needs to be maintainable (this, theoretically, could be done cleanly by storing custom error messages in the information schema, but SQL standard does not specify that so this is purely theoretical comment - in practice you will have to have your own tables for such purposes)

• 需要是可维护的（理论上，这可以通过在信息模式中存储自定义错误消息来干净地完成，但 SQL 标准没有指定，所以这纯粹是理论上的评论 - 在实践中，您必须为此目的拥有自己的表)

and, even more importantly

而且，更重要的是

• error message processing is context sensitive(and error handler would be most informed from the point of view of the data client - sometimes same error code might need different presentation, different message)

• 错误消息处理是上下文敏感的（从数据客户端的角度来看，错误处理程序会得到最多的通知 - 有时相同的错误代码可能需要不同的表现形式，不同的消息）

回答by Gary Myers

Whether the constraint gets raised to the client or logged in a file for (potential) analysis by support, you should have a more useful message.

无论是将约束提升到客户端还是登录到文件中以供支持人员进行（潜在）分析，您都应该收到更有用的消息。

If you name your constraint it gets more helpful.

如果你命名你的约束，它会更有帮助。

I'd go for something like

我会去做类似的事情

ALTER TABLE blah ADD CONSTRAINT blah_gender_ck CHECK ( Gender IN ('F', 'M', 'OTHER'));