This discussion is archived
12 Replies Latest reply: Mar 6, 2013 11:42 AM by HamidHelal RSS

ORA-06502:numeric or value errORA-04088: error during execution of trigger

user389605 Newbie
Currently Being Moderated
I received the following error message while entering a number within the maximum value (i.e. 9,999,999.) allowed in a data entry form which is separate from the base form:

ORA-06502: PL/SQL: numeric or value error:number precision too large ORA-06512:at "<Owner>.<Trigger>", line 194 ORA-04088: error during execution of trigger "<Owner>.<Trigger>"

Where <Owner> is the schema name and <Trigger> is the trigger name. The data block of the entry form is based on a database table, and the sum of all values entered (w/ a maximum value of 99,999,999.) is displayed in the base form. A grand total of this sum along w/ other totals on the base form is also displayed w/ a maximum value defined as 999,999,999. I only receive the above error message when the grand total is greater than 99,999,999. If the grand total is less than or equal to 99,999,999, the base form works fine. Why????

I have verified all the attributes for the database columns and form fields and all seems to be okay, and I am running out of my wits. I am desperately in need of your help in resolving this issue soon because pressure is on...

Thanks in advance for any/all the help.
  • 1. Re: ORA-06502:numeric or value errORA-04088: error during execution of trigger
    AhamedRafeeque Newbie
    Currently Being Moderated
    Dear Friend,

    Please make clear of your form field size and datatype and your database column size and datatype.

    Regards
    Ahamed Rafeeque Cherkala
  • 2. Re: ORA-06502:numeric or value errORA-04088: error during execution of trigger
    user389605 Newbie
    Currently Being Moderated
    The database column, say TOT_COST, is defined as NUMBER(9,2), and this column is a calculation field in the data entry form. The form field1, say S_TOT_COST, is defined as '99,999,990.' w/ data type = Number and maximum length = 11. The form field2, say G_TOT_COST, is defined as '99,999,999,990.' w/ data type = Number and maximum Length = 15. The S_TOT_COST value is later moved to a database column called TAB_S_TOT_COST which is defined as NUMBER(9). Both S_TOT_COST and G_TOT_COST fields are calculated and displayed in the base form.
  • 3. Re: ORA-06502:numeric or value errORA-04088: error during execution of trigger
    HamidHelal Guru
    Currently Being Moderated
    Orchid wrote:
    I received the following error message while entering a number within the maximum value (i.e. 9,999,999.) allowed in a data entry form which is separate from the base form:

    ORA-06502: PL/SQL: numeric or value error:number precision too large ORA-06512:at "<Owner>.<Trigger>", line 194 ORA-04088: error during execution of trigger "<Owner>.<Trigger>"

    Where <Owner> is the schema name and <Trigger> is the trigger name. The data block of the entry form is based on a database table, and the sum of all values entered (w/ a maximum value of 99,999,999.) is displayed in the base form. A grand total of this sum along w/ other totals on the base form is also displayed w/ a maximum value defined as 999,999,999. I only receive the above error message when the grand total is greater than 99,999,999. If the grand total is less than or equal to 99,999,999, the base form works fine. Why????

    I have verified all the attributes for the database columns and form fields and all seems to be okay, and I am running out of my wits. I am desperately in need of your help in resolving this issue soon because pressure is on...

    Thanks in advance for any/all the help.
    check your associated database column length. It's precision is not able to hold you said value. increase the length by
    ALTER TABLE table_name  
    MODIFY (column_name NUMBER(20) );
    it's above solution don't work. Then, probably you use variable in the trigger code which hold the vale and it's size is limited. increase it.

    added
    try this
    increase the length of TAB_S_TOT_COST 9 to 18.

    and your problem will solved.

    Hamid

    Edited by: HamidHelal on Feb 13, 2013 10:28 AM
  • 4. Re: ORA-06502:numeric or value errORA-04088: error during execution of trigger
    user389605 Newbie
    Currently Being Moderated
    Thanks much for your prompt response. Since S_TOT_COST is a format field, doesn't Oracle strip the ',' and '.' and only move the number of 99999990 during the database table update by using the 'set' command? and if so, why the length of NUMBER(9) database TAB_S_TOT_COST column is not large enough for S_TOT_COST to be moved to since S_TOT_COST is defined as '99,999,990.' ???
  • 5. Re: ORA-06502:numeric or value errORA-04088: error during execution of trigger
    HamidHelal Guru
    Currently Being Moderated
    Orchid wrote:
    Thanks much for your prompt response. Since S_TOT_COST is a format field, doesn't Oracle strip the ',' and '.' and only move the number of 99999990 during the database table update by using the 'set' command? and if so, why the length of NUMBER(9) database TAB_S_TOT_COST column is not large enough for S_TOT_COST to be moved to since S_TOT_COST is defined as '99,999,990.' ???
    '99,999,990.'
    it's just only format mask. If your actual value is 99 999 995.89 it will just show you 99 999 996

    But actual value length is 8,2 = 10.

    How can you store this length into 9 ?

    Hope you understand..


    hope this helps


    Hamid
  • 6. Re: ORA-06502:numeric or value errORA-04088: error during execution of trigger
    XeM Newbie
    Currently Being Moderated
    Hi,

    Change the format mask as 9999999.99 might work. you can try it.
  • 7. Re: ORA-06502:numeric or value errORA-04088: error during execution of trigger
    user389605 Newbie
    Currently Being Moderated
    Hello Hamid,

    Thanks for your help. Yes, it works as suggested after I increased the database table column length from NUMBER(9) to NUMBER(11). When I tried the following SQL code in a command line, I received no errors:
    create table TAB_TOT_COST(TAB_SEQ_NO NUMBER, TAB_TOT_MATL NUMBER(9));
    
    insert into TAB_TOT_COST VALUES (3191391, 999999992);
    
    select * from TAB_TOT_COST;
    
    Output:
    
    TAB_SEQ_NO     TAB_TOT_MATL
    -----------------     -------------------
    3191391          999999992
    
    update TAB_TOT_COST
    set TAB_TOT_MATL = 999999999.47;
    
    select * from TAB_TOT_COST;
    
    Output:
    
    TAB_SEQ_NO     TAB_TOT_MATL
    -----------------          --------------------
    3191391                 999999999
    Apparently, Oracle database truncates the decimal points and only update the 9 digits value without giving any errors, but the Oracle form application is more picky and it generates both Oracle and forms error messages when running the application. It looks like there is a difference when executing the sql commands in Oracle database and running these commands in Oracle form application. Do you agree?

    Thanks again for all the help.
  • 8. Re: ORA-06502:numeric or value errORA-04088: error during execution of trigger
    HamidHelal Guru
    Currently Being Moderated
    Orchid wrote:
    Hello Hamid,

    Thanks for your help. Yes, it works as suggested after I increased the database table column length from NUMBER(9) to NUMBER(11).
    who give you and the correct answer, you didn't mark it ;)

    >
    Apparently, Oracle database truncates the decimal points and only update the 9 digits value without giving any errors, but the Oracle form application is more picky and it generates both Oracle and forms error messages when running the application. It looks like there is a difference when executing the sql commands in Oracle database and running these commands in Oracle form application. Do you agree?
    Yes
    Thanks again for all the help.
    You can only give reward for this by marking helpful and correct answer.
  • 9. Re: ORA-06502:numeric or value errORA-04088: error during execution of trigger
    user389605 Newbie
    Currently Being Moderated
    Thanks Hamid for letting me know and you are so deserve the reward. Hope I did it this time.
  • 10. Re: ORA-06502:numeric or value errORA-04088: error during execution of trigger
    user389605 Newbie
    Currently Being Moderated
    From further testing, the problem as described in the message description posted on Feb 12, 2013 7:12 PM still exists. Thus I need more help in resolving the problem.
  • 11. Re: ORA-06502:numeric or value errORA-04088: error during execution of trigger
    user389605 Newbie
    Currently Being Moderated
    Hi Hamid and All,

    Sorry, I prematurely thought this problem was resolved but it still exists after increasing the length of the targeted column from NUMBER(9) to NUMBER(11). I tried to use the round and trunc function to suppress the two digit of decimal while assigning the value without the decimal points before updating the dB table but, I still received the following error message:

    ORA-06502: PL/SQL: numeric or value error:number precision too large ORA-06512:at "<Owner>.<Trigger>", line 194 ORA-04088: error during execution of trigger "<Owner>.<Trigger>"

    Where <Owner> is the schema name and <Trigger> is the trigger name. FYI, a trigger was fired when certain columns of the dB table were updated, and I, believe, the application bombed when the trigger was being executed. I have examined all the related columns of the subsequent tables being updated in the procedures/functions of the package which was triggered when a particular column was updated, but all seems to be fine.

    If you or anyone else could take a fresh look to help in resolving the problem, I would really appreciate any/a
    ll the help.

    Thanks millions!
  • 12. Re: ORA-06502:numeric or value errORA-04088: error during execution of trigger
    HamidHelal Guru
    Currently Being Moderated
    Ok.
    First disable the trigger. and check data are inputted the table correctly. Think it will work.

    you posted problem is
    ORA-06502: PL/SQL: numeric or value error:number precision too large ORA-06512:at "<Owner>.<Trigger>", line 194 ORA-04088: error during execution of trigger "<Owner>.<Trigger>"
    and clearly it says problem is arround Line No 194 of you trigger code.
    Can you please post the code with line number ?

    Hamid

    Edited by: HamidHelal on Mar 7, 2013 1:37 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points