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?
My first thought is an additional table in the database with all the
constraint names and corresponding text messages in it. In case of an error
every frontend has to read this information from the table and displays it
to the user.

Thanks,
Thomas

Fri, 10 Dec 2004 09:43:30 +0100 Thomas Blankschein <thomas (AT) blankschein (DOT) de>
wrote:

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)

On Fri, 10 Dec 2004 09:43:30 +0100, "Thomas Blankschein"
<thomas (AT) blankschein (DOT) de> wrote:

on server error triggers in 8i and higher.


--
Sybrand Bakker, Senior Oracle DBA