遇到一个有点意思的问题,在执行如下一段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了.....真是诡异啊...........
以后再碰到这样的问题,得多“长个心眼”了 ~~
还不清楚导致这个问题的根本原因是啥, 先记录在此,以作前车之鉴......