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

Question

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

Answer 1

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.

Answer 2

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)