[PLSQL]ORA-01426: numeric overflow ?? !!
遇到一个有点意思的问题,在执行如下一段code的时候,出现了 numeric overflow的错误.....
frank@ORCL> l
1 declare
2 v_test_number number;
3 begin
4 v_test_number := 1000*60*60*24*365;
5* end;
frank@ORCL> /
declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 4
1 declare
2 v_test_number number;
3 begin
4 v_test_number := 1000*60*60*24*365;
5* end;
frank@ORCL> /
declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 4
从给出的错误提示来看,貌似是1000*60*60*24*365超出了number类型所能容纳的范围了,但是...
frank@ORCL> select 1000*60*60*24*365, length(to_char(1000*60*60*24*365)) from dual;
1000*60*60*24*365 LENGTH(TO_CHAR(1000*60*60*24*365))
----------------- ----------------------------------
3.1536E+10 11
1000*60*60*24*365 LENGTH(TO_CHAR(1000*60*60*24*365))
----------------- ----------------------------------
3.1536E+10 11
可以看到1000*60*60*24*365的值的长度为才是11,而NUMBER可以支持到整数位长度为38的数字,因此这个值远远没有到numeric overflow的情况。但是为啥Oracle会报出这么一个错误呢? 真的很费解.......
搜了下对这个ORA-error的解释,如下所示......
Error: |
ORA-01426: numeric overflow |
Cause: |
You tried to evaluate an expression that resulted in a numeric overflow (or underflow). |
Action: |
The options to resolve this Oracle error are:
|
看到Action部分有说到要减少operand的个数,难道是1000*60*60*24*365 这5个数在一起连乘都不行吗? 测试下分成两步来做,情况如何.....
-- 不死心,再试一次,还是出错........
frank@ORCL> variable v_test number
frank@ORCL> exec :v_test := 1000*60*60*24*365;
BEGIN :v_test := 1000*60*60*24*365; END;
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 1
-- 好吧,再声明一个变量,分两步走.....
frank@ORCL> variable v_test2 number;
frank@ORCL> exec :v_test := 1000*60*60*24;
PL/SQL procedure successfully completed.
frank@ORCL> exec :v_test2 := :v_test * 365;
PL/SQL procedure successfully completed.
frank@ORCL> variable v_test number
frank@ORCL> exec :v_test := 1000*60*60*24*365;
BEGIN :v_test := 1000*60*60*24*365; END;
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 1
-- 好吧,再声明一个变量,分两步走.....
frank@ORCL> variable v_test2 number;
frank@ORCL> exec :v_test := 1000*60*60*24;
PL/SQL procedure successfully completed.
frank@ORCL> exec :v_test2 := :v_test * 365;
PL/SQL procedure successfully completed.
可以看到,这样做就OK了.....真是诡异啊...........
以后再碰到这样的问题,得多“长个心眼”了 ~~
还不清楚导致这个问题的根本原因是啥, 先记录在此,以作前车之鉴......
--------------------------------------
Regards,
FangwenYu