Re: presenting user-friendly error messages

Fri, 10 Dec 2004 09:43:30 +0100 Thomas Blankschein <thomas_at_blankschein.de> wrote:

> Hello,
>
> an Oracle-DB with several constraints does complain correctly if a user
> tries to insert data over the frontends which do not fit to the check
> constraints. Unfortunately in such a case only an message like 'ORA-XXXX
> Constraint CHK_CUSTOMER_ID violated' is displayed.
> How can I present a more informative message to the user with a
> description
> of the error and hints to avoid it? And this independend from the
> frontend?

Independent from the frontend? For that you have to build a server side software layer (Pl/sql packages for example) which catches the exception und returns a readable and context driven message to the frontend.
As a workaround (but IMO it is not a good design) you may write a pre-insert trigger for the table in question. In the code of the trigger you may check the constraint with procedural logic and then use "raise_application_error()" to return a context driven message to the frontend. In this case don't remove the declarative check-constraint  from the table! It's good for documentation and also if the trigger once vanishes :-)

Regards

-- 
Frank Piron,
defrankatkonaddot
(leftrotate two)