[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 < sysdate-interval '100' day;
select * from emp  where hiredate < sysdate-interval '100' day
                                                     *
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small

SQL> select * from emp  where hiredate < sysdate-interval '99' day;
     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 < sysdate-interval '99' day
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 < sysdate-interval '100' day(3);

SQL> select * from emp  where hiredate < sysdate-interval '100' day(3);
     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 < sysdate-interval '99' day(3);
select * from emp  where hiredate < sysdate-interval '199' day(3);
--通过

SQL> select * from emp  where hiredate < sysdate-interval '1199' day(3);
select * from emp  where hiredate < sysdate-interval '1199' day(3)
                                                     *
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small

--看来与天数的位数有关.

我的建议还是不要采用如下写法.
直接减去一个数字就可以了.
select * from emp  where hiredate < sysdate-99;