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

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

Is there way to provide developer-defined message like (pseudo)Java's

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

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 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 thi开发者_JS百科s. So it's time for me to be good citizen and finally accept an answer (sorry, just forgot about that).

---

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 possibly cleaned 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.

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

---

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;

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.

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.

---

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.

---

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 presentation of 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

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.

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)

moving constraints to triggers is ugly (is it?), so I have to find smth different from 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).

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

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)

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)

---

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'));