Thread: help handling error with trigger

dear one,

create table smita(n number,name varchar2(30));
insert into smita values(1,'iii');
insert into smita values(2,'iii');
insert into smita values(3,'iii');
insert into smita values(4,'iii');

CREATE OR REPLACE TRIGGER tg_smita
after insert or update on smita
declare
v_count number(9);
begin
select count(*) into v_count from smita;
if v_count > 0 then
raise_application_error(-20777,'already exist');
end if;
end;



insert into smita values(7,'iii')


ORA-20777: already exist
ORA-06512: at "IPLSMCDEV.TG_SMITA", line 6
ORA-04088: error during execution of trigger 'IPLSMCDEV.TG_SMITA'

i want to display only ora-20777 msg
and dont want ora-06512

how can i do ?
pls help


smita.

Hi Smitai,

ORA-6512 is the common error code reported for all plsql error messages. The error stack is always returned and i think there is no possible method to suppress or avoid them.

*** Innila ***

Hiiiiiii
Thanks..
u know what it is possible in java to hide some error so i think there must b some way to hide this in oracle also ...
bcos it is not user friendly and unwanted thing for client sake...

THANKS...
But can u please edit my prog trig and let me know how can i do it .
I think it not working...

Originally Posted by kalayama

Well, you can write exception handlers. And display nothing if the error code ORA-06512 is returned. Code such that, you handle only the exceptions you want and ignore the rest. This should solve your problem because you want to handle only one exception. If you would like to learn exception handling methodically, here's the link (Official Oracle link).

Exception Hendling In Oracle

-Kalayama

hi,

thanks dear for posting such a good question, really its rare to see good questions.

well, the real answer for this problem is that, error stack is always
returned, there is no way to avoid it.

i did lot of experiment on this, and there is a way to handle this, let me post the solution then i will discuss the disadvantage of this solution.

step1: create a procedure, to raise the application error. like this:

sql>create or replace procedure error_msg
2 as
3 begin
4 raise_application_error( -20777, 'already exist' );
5 end;
6 /

Procedure created.

step2: set serverout on;

stpe2: modify the trigger code a bit like this:

SQL> CREATE OR REPLACE TRIGGER tg_smita
2 after insert or update on smita
3 declare
4 v_count number(9);
5 begin
6 select count(*) into v_count from smita;
7 if v_count > 0 then
8 error_msg;
9 end if;
10 exception when others
11 then
12 dbms_output.put_line(SQLERRM);
13* end;
14 /

Trigger created.

step 3: execute the insert statement to, fire the trigger.

SQL> insert into smita values(7,'iii');
ORA-20777: already exist
1 row created.


well. see u got your raise application message. but see carefully its showing one more line, highlighted in blue colour. now the next question is, how to prevent this line from getting inserted. i tried a lot, but in vain, if anyone finds the solution then do post reply.

well, this methos will work for many other cases, so we can use this. but it fails in scenarios like the present case, especially in the triggers.

i gave the idea, now u all experiment how to prevent that row from getting inserted.

and think off hwo the procedure declared and the change in the trigger code is handling the error stack. because that's very intersting. i thought to explain, but its ok.... let everyone think of it. if u are not able to get the logic, then i will give the answer.

regards,
RSY

well even if use set feeback off, the message will not be displayed, but the row will be inserted. which actually should not happen.lets wait for some one to sort out this problem

Regards,
RSY

Hiiiiiiii
Raghav
Thanks for trying my question......
Can you tell me
this stack error like ora-06512 must be store somewhere ,in a table or any dictionary,view after getting raise .
we can take the help of this.
i tried to craete a proc and trap this err in trig exception with 'already exist'
msg.
it is funny it outputed two time time this err.
so it fail to handle it with pragma_exception_init..
pls observe if you get...
thanks