|
ORA-01426 Numeric Overflow after Upgrade to 10g [ID 809999.1] | ||
|
| ||
|
|
修改时间20-OCT-2010 类型 PROBLEM 状态 PUBLISHED |
|
In this Document
Symptoms
Cause
Solution
References
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.4 to 11.1.0.7 - Release: 10.2 to 11.1
Information in this document applies to any platform.
***Checked for relevance on 20-Oct-2010***
Symptoms
After database upgrade from 9i to 10g the following error is returned when multiplying numbers in PLSQL.
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 1
The same PL/SQL code worked in 9.2.0.8 but fails in 10.2.0.4 and 11.1.0.7.
Cause
This issue is caused by Multiplication Doesn't work in PLSQL with 10.1.0.3. This is a duplicate of unpublished base Bug 3591135 Calculating 256*256*256*256 causes ORA-1426: NUMERIC OVERFLOW.
This issue is caused by a change in behaviour between 9i and 10g. 9i uses numeric computation (38 significant digits) and 10g uses integer arithmetic (9 significant digits). Evaluation of numeric literals has changed in 10g such that at least one of the constants in a numeric computation with literals must be decimal specified to the 10th place. For example, if one of the numbers in the computation is 16, it can be
specified as 16.0 to resolve the issue.
Solution
To implement the solution, please execute the following steps:
Add a decimal place to one of the multiples:
For example, instead of:
SQL> var ret number;
SQL> exec :ret := 9999 * 1000000000;
BEGIN :ret := 9999 * 1000000000; END;
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 1
Add a decimal place to the end of one of the multiples:
SQL> exec :ret := 9999 * 1000000000.0;
PL/SQL procedure successfully completed.
References
- MULTIPLICATION DOESN'T WORK IN PLSQL WITH 10.1.0.3