Error messages on constraint Violation

[MikeJones]

Hi all,

I'm sure that this is possible and that I didn't just dream it, can any body help??

I need to set up a unique constraint on a column. If the users try to break the constraint though I don't want it to come up with:
ORA-00001: unique constraint (schema.constraint name) violated

I'd like to put a different peice of text in there somewhere. Something like "That thingy already exists dummy"

 

[listener22]

in the beginning of your procedure,

/* declare an exception */
<DECLARE> already_exists EXCEPTION;

/* associate the exception with an Oracle exception */
PRAGMA EXCEPTION_INIT (already_exists, -1);

BEGIN
... code ...

EXCEPTION
when already_exists then

dbms_output.put_line ( etc. (or whatever you want to do here)

END; Jim

oracle, vb

 

[MikeJones]

Thanks for that, but is it not possible to do it at the point that you declare the Constraint?? I'm sure I've done it before but can't find my documentation!!

Thanks in advance,

Mike.

 

[LokiDba]

Above is good practise because it documents the exception that is being raised. But you there is a standard Oracle exception for ORA-00001 which is DUP_VAL_ON_INDEX. So the exception handler would be as follows:

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN

DBMS_OUTPUT.PUT_LINE( &quot;What Ever&quot; ):

END ;

 

[MikeJones]

Thanks again but to re-iterate my question I want to associate the error with the constraint not with the breaking of it. The problem is that several bits of code may break the index and they should all output the same error message. Therefore I'd like to declare the error message in one place rather than have several exception handlers all over the place doing the same thing.

Cheers,

Mike.

 

[MikeJones]

Further to my last posting here is what you can do in your trigger (I hope!) You will need to make sure though that you stop the trigger looping progrmatically and you better get it right too or I would have thought you'll cause the DB to crash...

In this example I'm only selecting from the table but try it with an insert I think it should still work.

--------------------------

create or replace trigger mikes_test
after insert or update or delete
on your_table
for each row
--
declare
v_count number;
mutant_trigger exception;
pragma exception_init(mutant_trigger, -4091);
begin
select count(*) into v_count from your_table;
--
dbms_output.enable;
dbms_output.put_line(v_count);
exception
when mutant_trigger then null;
end;
.
/
set arraysize 1
show errors