[20141004]ORA-01873 the leading precision of the interval is too small.txt
--前几天运行的报表,出现错误ORA-01873 the leading precision of the interval is too small.txt.
--出现错误语句含有如下:
to_date('2014-09-30 23:59:59')-interval '120' day;
$ oerr ora 1873
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.
--我还第一次看到有人在程序中interval,如果把前面改成99小于100的天数,程序可以通过.
SQL> select * from emp where hiredate select * from emp where hiredate *
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
SQL> select * from emp where hiredate EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
...
SQL> @dpc '' ''
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID 3c476wmxtgbum, child number 0
-------------------------------------
select * from emp where hiredate Plan hash value: 3956160932
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
|* 1 | TABLE ACCESS FULL| EMP | 14 | 3 (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("HIREDATE" SECOND(0))
--注意看day后面有一个括号(2),是否改写如下可以通过.
select * from emp where hiredate
SQL> select * from emp where hiredate EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
...
--可以通过.
select * from emp where hiredate select * from emp where hiredate --通过
SQL> select * from emp where hiredate select * from emp where hiredate *
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
--看来与天数的位数有关.
我的建议还是不要采用如下写法.
直接减去一个数字就可以了.
select * from emp where hiredate
