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