Results 1 to 11 of 11

Thread: help handling error with trigger

  1. #1
    Junior Member
    Join Date
    Feb 2007
    Answers
    17

    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.


  2. #2
    Expert Member
    Join Date
    Sep 2006
    Answers
    130

    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 ***


  3. #3
    Junior Member
    Join Date
    Feb 2007
    Answers
    17

    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...


  4. #4
    Expert Member
    Join Date
    Sep 2006
    Answers
    130

    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....

    *** Innila ***

  5. #5
    Expert Member
    Join Date
    Sep 2006
    Answers
    477

    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).

    Exception Hendling In Oracle

    -Kalayama

    [COLOR="Blue"][SIZE="2"]"If you are not living on the edge of your life, you are wasting space"[/SIZE][/COLOR]

    Someone says "Impossible is nothing". The man next him says "Let me see you licking your elbow tip!"

  6. #6
    Junior Member
    Join Date
    Feb 2007
    Answers
    17

    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...




    Quote Originally Posted by kalayama View Post
    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



  7. #7
    Expert Member
    Join Date
    Sep 2006
    Answers
    477

    Re: help handling error with trigger

    I am sorry smitai 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 ).

    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

    Last edited by kalayama; 02-27-2007 at 05:29 AM. Reason: Typo
    [COLOR="Blue"][SIZE="2"]"If you are not living on the edge of your life, you are wasting space"[/SIZE][/COLOR]

    Someone says "Impossible is nothing". The man next him says "Let me see you licking your elbow tip!"

  8. #8
    Contributing Member
    Join Date
    Jan 2007
    Answers
    31

    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 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


  9. #9
    Expert Member
    Join Date
    Sep 2006
    Answers
    130

    Re: help handling error with trigger

    Quote Originally Posted by raghav_sy View Post
    hi,

    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.

    regards,
    RSY
    Hi,

    Good one Raghav. Definitly it should help Smita.

    The feedback problem "1 row created" can be eliminated by using,
    set feedback off

    *** Innila ***

  10. #10
    Contributing Member
    Join Date
    Jan 2007
    Answers
    31

    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


  11. #11
    Junior Member
    Join Date
    Feb 2007
    Answers
    17

    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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact