Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2004
    Location
    Greece
    Posts
    23

    Unanswered: ORA-06512 & ORA-04088 error message but trigger compiles without errors!

    I use the following trigger and it compiles normally without errors:

    create or replace trigger check_pososto
    before insert or update of PER_PROP on IDIOKTISIA
    for each row
    declare
    sum PROPERTY.PER_PROP%TYPE;
    begin
    SELECT SUM(PER_PROP) INTO sum FROM PROPERTY WHERE LIC_NUM = :new.LIC_NUM;
    if((athroisma+ :new.PER_PROP )>100) then
    raise_application_error(-20236, 'Vehicle' || :new.LIC_NUM|| ' has property percentage bigger than that it can be given|| to_char(100 - sum));
    end if ;
    end;
    /


    But when it fires it works normally but i get also the following errors:

    ERROR at line 1:
    ORA-20236: Vehicle XAA1000 has property percentage bigger than that it can gets 10
    ORA-06512: at "PAED1.CHECK_PERC", line 6
    ORA-04088: error during execution of trigger 'PAED1.CHECK_PERC'

  2. #2
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    What's "athroisma" ?

  3. #3
    Join Date
    Jan 2004
    Location
    Greece
    Posts
    23
    Ooops!Sorry i translated it from Greek and forgot to change this.
    It's sum.


    create or replace trigger check_pososto
    before insert or update of PER_PROP on IDIOKTISIA
    for each row
    declare
    sum PROPERTY.PER_PROP%TYPE;
    begin
    SELECT SUM(PER_PROP) INTO sum FROM PROPERTY WHERE LIC_NUM = :new.LIC_NUM;
    if((sum + :new.PER_PROP )>100) then
    raise_application_error(-20236, 'Vehicle' || :new.LIC_NUM|| ' has property percentage bigger than that it can be given|| to_char(100 - sum));
    end if ;
    end;
    /
    Last edited by subwrc; 01-24-04 at 15:23.

  4. #4
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by subwrc
    Ooops!Sorry i translated it from Greek and forgot to change this.
    It's sum.


    create or replace trigger check_pososto
    before insert or update of PER_PROP on IDIOKTISIA
    for each row
    declare
    sum PROPERTY.PER_PROP%TYPE;
    begin
    SELECT SUM(PER_PROP) INTO sum FROM PROPERTY WHERE LIC_NUM = :new.LIC_NUM;
    if((sum + :new.PER_PROP )>100) then
    raise_application_error(-20236, 'Vehicle' || :new.LIC_NUM|| ' has property percentage bigger than that it can be given|| to_char(100 - sum));
    end if ;
    end;
    /
    So it means that (100 - sum) = 10 => sum = 90
    So 10 + :new.PER_PROP > 100 => :new.PER_PROP > 90

    Look for insert or updates that tries to set the column PER_PROP to a value > 90 ...

    HTH
    AL

  5. #5
    Join Date
    Jan 2004
    Location
    Greece
    Posts
    23
    Originally posted by alberto.dellera
    .....
    So 10 + :new.PER_PROP > 100 => :new.PER_PROP > 90

    Look for insert or updates that tries to set the column PER_PROP to a value > 90 ...

    HTH
    AL

    Can't understand that "So 10 + :new.PER_PROP > 100 => :new.PER_PROP > 90" you are saying.

    I have some records in database with sum of percentage of property(PER_PROP) for a license number (LIC_NUM )that equals 90,then i can add a record for that LIC_NUM with maximum PER_PROP=10.
    So when i try to enter it with a PER_PROP=20 it will fire normally as it should and not enter the record.
    But why it displays those messages?

  6. #6
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by subwrc
    So when i try to enter it with a PER_PROP=20 it will fire normally as it should and not enter the record.
    But why it displays those messages?
    Your code says, in the case you mention above, "if PER_PROP is greater then 10, raise an exception (error) with the code -20236". This error makes Oracle abort the insert/update and throw back the error to the caller, which displays the error.

    Please note that he whole statement is aborted - if you issue e.g.

    update IDIOKTISIA set PER_PROP=20;

    when the trigger fires on the row with sum=90, the error is generated, and ALL the rows that were previously updated by the statement are rolled back to their original value.

    Al

  7. #7
    Join Date
    Jan 2004
    Location
    Greece
    Posts
    23
    IDIOKTISIA = PROPERTY

    Ok for an update statement i should do a different semantic approach.
    So i change the code to this(just added ld.PER_PROP inside second if) :

    create or replace trigger check_pososto
    before insert or update of PER_PROP on PROPERTY
    for each row
    declare
    sum PROPERTY.PER_PROP%TYPE;

    begin

    if inserting
    SELECT SUM(PER_PROP) INTO sum FROM PROPERTY WHERE LIC_NUM = :new.LIC_NUM;
    if((sum + :new.PER_PROP )>100) then
    raise_application_error(-20236, 'Vehicle' || :new.LIC_NUM|| ' has property percentage bigger than that it can be given||

    to_char(100 - sum));
    end if ;
    end if ;

    if updating
    SELECT SUM(PER_PROP) INTO sum FROM PROPERTY WHERE LIC_NUM = :new.LIC_NUM;
    if((sum + :new.PER_PROP - ld.PER_PROP )>100) then
    raise_application_error(-20236, 'Vehicle' || :new.LIC_NUM|| ' has property percentage bigger than that it can be given||

    to_char(100 - sum));
    end if ;
    end if;

    end;
    /


    But where these error messages come from?
    Last edited by subwrc; 01-25-04 at 09:28.

  8. #8
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by subwrc
    But where these error messages come from?
    Maybe i'm not understanding what you really mean, but .. they come from the raise_application_error second parameter:

    SQL> drop table t;

    Table dropped.

    SQL> create table t (x number);

    Table created.

    SQL> create or replace trigger t_trig
    2 before insert or update
    3 on t
    4 for each row
    5 begin
    6 raise_application_error (-20236, 'What a wonderful day');
    7 end;
    8 /

    Trigger created.

    SQL> insert into t values (2);
    insert into t values (2)
    *
    ERROR at line 1:
    ORA-20236: What a wonderful day
    ORA-06512: at "DELLERA.T_TRIG", line 2
    ORA-04088: error during execution of trigger 'DELLERA.T_TRIG'


    SQL> select * from t;

    no rows selected

    In your trigger, the expression "(sum + :new.PER_PROP )>100" or the other evaluates to true, the raise_application_error is invoked, the trigger stops executing, and the error makes the statement fail.

    Or did you mean something else ?

  9. #9
    Join Date
    Jan 2004
    Location
    Greece
    Posts
    23
    You have understood right.
    Even in your simple example can't understand where those errors come from.
    Could you explain me?

  10. #10
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    raise_application_error (-20236, 'What a wonderful day');

    means

    "send an error with code -20236" to caller.

    The second parameter is the error message, that can be anything you like.

  11. #11
    Join Date
    Jan 2004
    Location
    Greece
    Posts
    23
    Yes i know that.
    I mean the error messages that follow the error that i raise.
    ORA-06512 and ORA-04088 why appear?That's my problem.

  12. #12
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by subwrc
    Yes i know that.
    I mean the error messages that follow the error that i raise.
    ORA-06512 and ORA-04088 why appear?That's my problem.
    Ahh it's clear now!

    They are not separate errors, it's just one error (the one you raised) that gets printed that way.

    Read that as

    ORA-20236: Vehicle XAA1000 has property percentage bigger than that it can gets 10 "this is the error that was raised"
    ORA-06512: at "PAED1.CHECK_PERC", line 6 "the error was on line 6"
    ORA-04088: error during execution of trigger 'PAED1.CHECK_PERC' "look above for more infos"

    ORA-06512 is similar to a stack trace in Java - if you call a procedure from the trigger, and the procedure raise an errors, you will get something like

    ORA-20236: Error, nuclear war declared
    ORA-06512: at "PAED1.MY_PROCEDURE", line 89
    ORA-06512: at "PAED1.CHECK_PERC", line 6
    ORA-04088: error during execution of trigger 'PAED1.CHECK_PERC'

    HTH
    Al

  13. #13
    Join Date
    Jan 2004
    Location
    Greece
    Posts
    23
    So it's nothing to worry about right?
    The trigger works fine uh?
    Just supplemental info about where in my code was the error that i raised,right?
    I can ignore them ?

  14. #14
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by subwrc
    So it's nothing to worry about right?
    The trigger works fine uh?
    Just supplemental info about where in my code was the error that i raised,right?
    I can ignore them ?
    Exactly - you can think of them as a "multirow error message" .

  15. #15
    Join Date
    Jan 2004
    Location
    Greece
    Posts
    23
    Thanks a lot alberto!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •