help handling error with trigger
:confused:
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 ?:confused:
pls help :(
smita.
Re: help handling error with trigger
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 ***
Re: help handling error with trigger
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...
Re: help handling error with trigger
Actually, i could understand your problem, because i faced similar complexity for one of my procedure.
Let me try to analyse on this issue....
Re: help handling error with trigger
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).
[URL="http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm"]Exception Hendling In Oracle[/URL]
-Kalayama
Re: help handling error with trigger
THANKS...
But can u please edit my prog trig and let me know how can i do it .
I think it not working...:p
[QUOTE=kalayama;8303]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).
[URL="http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm"]Exception Hendling In Oracle[/URL]
-Kalayama[/QUOTE]
Re: help handling error with trigger
I am sorry [B]smitai[/B] I do not have the luxury of spare time to edit your code. Moreover, it is always better to teach a man to catch fish rather than just feed the man with the fish.
You have been shown the door, you have to walk through it(One of my fav dialogues from the movie Matrix:p ).
The point is, first you try reediting your code, use the techniques given in the article. And if you still face a problem there, feel free to ask....
But, above all, my most basic problem is, I have no time! Sorry mate...
-Kalayama
Re: help handling error with trigger
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 [COLOR="Red"]error_msg;[/COLOR]
9 end if;
10 [COLOR="red"]exception when others[/COLOR]
11 [COLOR="red"]then[/COLOR]
12 [COLOR="red"]dbms_output.put_line(SQLERRM);[/COLOR]
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
[COLOR="Blue"]1 row created.[/COLOR]
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
Re: help handling error with trigger
[QUOTE=raghav_sy;8377]hi,
SQL> insert into smita values(7,'iii');
ORA-20777: already exist
[COLOR="Blue"]1 row created.[/COLOR]
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.
regards,
RSY[/QUOTE]
Hi,
Good one Raghav. Definitly it should help Smita.
The feedback problem "1 row created" can be eliminated by using,
[B]set feedback off[/B]
Re: help handling error with trigger
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
Re: help handling error with trigger
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