[20181017]ORA-01873 the leading precision of the interval is too small.txt
https://jonathanlewis.wordpress.com/2018/10/17/problem-solving/
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
SCOTT@test01p> SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.FF') + NUMTODSINTERVAL(2850166802000/1000, 'SECOND') FROM DUAL;
SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.FF') + NUMTODSINTERVAL(2850166802000/1000, 'SECOND') FROM DUAL
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
D:\tools\rlwrap>oerr ora 1873
01873, 00000, "the leading precision of the interval is too small"
// *Cause: The leading precision of the interval is too small to store the
// specified interval.
// *Action: Increase the leading precision of the interval or specify an
// interval with a smaller leading precision.
--//按照作者解析:
Problem identified – it's a numeric limit of the numtodsinterval() function. Interestingly it's not documented in the
Oracle manuals, in fact the SQL Reference manual suggests that this shouldn't be a limit because it says that "any
number value or anything that can be cast as a number is legal" and in Oracle-speak a number allows for roughly 38
digits precision.
SCOTT@test01p> SELECT NUMTODSINTERVAL(power(2,31)-1, 'SECOND') from dual;
NUMTODSINTERVAL(POWER(2,31)-1,'SECOND')
---------------------------------------------------------------------------
+000024855 03:14:07.000000000
--//2850166802000/1000-2^31 = 702683154,远远大于2^31.
--//改用分钟OK.
SCOTT@test01p> SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.FF') + NUMTODSINTERVAL(2850166802000/1000/60, 'MINUTE') FROM DUAL;
TO_TIMESTAMP('1970-01-0100:00:00.0','YYYY-MM-DDHH24:MI:SS.FF')+NUMTODSINTER
---------------------------------------------------------------------------
2060-04-26 01:00:02.000000000
--//突然想起以前的测试:
--//链接:[20180927]ora-01426.txt=>http://blog.itpub.net/267265/viewspace-2215090/
set serveroutput on
DECLARE
L_NUMBER number;
BEGIN
L_NUMBER := 1024 * 1024 * 1024 * 1024;
DBMS_OUTPUT.PUT_LINE(L_NUMBER);
END;
/
DECLARE
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 4
$ oerr ora 1426
01426, 00000, "numeric overflow"
// *Cause: Evaluation of an value expression causes an overflow/underflow.
// *Action: Reduce the operands.
set serveroutput on
DECLARE
L_NUMBER number;
a number;
BEGIN
a := 1;
L_NUMBER := a * 1024 * 1024 * 1024 * 1024;
DBMS_OUTPUT.PUT_LINE(L_NUMBER);
L_NUMBER := 1024 * 1024 * 1024 * 1024;
DBMS_OUTPUT.PUT_LINE(L_NUMBER);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
dbms_output.put_line( dbms_utility.format_error_backtrace);
END;
/
1099511627776
ORA-01426: numeric overflow
ORA-06512: at line 10
PL/SQL procedure successfully completed.
--//第1个赋值左边有变量存在,所以正常. 而第2个全部是常量,超出2^31报错.
--//很明显oracle在一个算式里面达到2^31 就报错.改成如下也不会报错
DECLARE
L_NUMBER number;
BEGIN
-- L_NUMBER := 2147483647-1+1;
L_NUMBER := 2147483647;
L_NUMBER := L_NUMBER+1-1;
DBMS_OUTPUT.PUT_LINE(L_NUMBER);
END;
/
--//我觉得这个问题与前面的问题实际上还不同.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2216855/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2216855/