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