[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



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;
/

--//我覺得這個問題與前面的問題實際上還不同.