[20181017]ORA-01873 the leading precision of the interval is too small.txt

[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; / --//我觉得这个问题与前面的问题实际上还不同.