ORA-01873: the leading precision

Question

I am trying to query view but getting "ORA-01873: the leading precision of the interval is too small" error message. Below is the query.

Select * from table order by Col1.

Below is view structure:

Col1    NOT NULL NUMBER
Col2    NOT NULL NVARCHAR2(80)
Col3    NOT NULL NUMBER
Col4    NOT NULL VARCHAR2(10)
Col5        NVARCHAR2(80)
Col6        NVARCHAR2(255)
Col7        NUMBER
Col8    NOT NULL NVARCHAR2(255)
Col9    NOT NULL NVARCHAR2(1)
Col10   NOT NULL NUMBER
Col11       VARCHAR2(19)
Col12       VARCHAR2(19)
Col13       VARCHAR2(19)
Col14       VARCHAR2(19)
Col15       VARCHAR2(19)
Col16       VARCHAR2(19)
Col17       NUMBER
Col18       NVARCHAR2(255)
Col19       NVARCHAR2(80)
Col20   NOT NULL NUMBER

Below is View Definition:

SELECT tab2.cola AS Col1,
    tab1.col AS COl2,    
    tab2.colb AS Col3,
    tab7.col AS Col4,
    DECODE(tab3.col, NULL, tab4.col) AS COl5,
    tab8.col AS Col6,
    tab6.col AS COl7,
    tab2.Colc AS Col8,
    tab2.cold AS Col9,
    tab2.cole AS Col10,
    TO_CHAR(TO_DATE('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') + NUMTODSINTERVAL( tab2.colf / 1000,'SECOND'), 'YYYY/MM/DD HH24:MI:SS') AS COl11,
    TO_CHAR(TO_DATE('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') + NUMTODSINTERVAL( tab2.colg / 1000,'SECOND'), 'YYYY/MM/DD HH24:MI:SS') AS Col12,
    TO_CHAR(TO_DATE('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') + NUMTODSINTERVAL( tab2.colh / 1000,'SECOND'), 'YYYY/MM/DD HH24:MI:SS') AS Col13,
    TO_CHAR(TO_DATE('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') + NUMTODSINTERVAL( tab2.coli / 1000,'SECOND'), 'YYYY/MM/DD HH24:MI:SS') AS Col14,
    TO_CHAR(TO_DATE('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') + NUMTODSINTERVAL( tab2.colj / 1000,'SECOND'), 'YYYY/MM/DD HH24:MI:SS') AS COl15,
    TO_CHAR(TO_DATE('1970/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') + NUMTODSINTERVAL( tab2.colk / 1000,'SECOND'), 'YYYY/MM/DD HH24:MI:SS') AS COl16,
    tab2.coll AS Col17,
    tab9.col AS Col18,
    tab10.col AS Col19,
    tab2.colm AS Col20
FROM tab1 ,
    tab2 ,
    tab3,
    tab4,
    tab5,
    tab6,
    tab7,
    tab8,
    tab9,
    tab10
WHERE ....

Other queries are running fine like select * from table where Col1 = 123 or select * from table where Col2 = 'xyz' order by Col1 but above query is not working for any col. Please suggest.

Answer 1

One of your numeric 'epoch' numbers appears to be too large (or too small) for the numtodsinterval() function to handle. The biggest value you can pass as the number of seconds is 2^31-1:

SQL> select numtodsinterval(power(2,31) - 1, 'SECOND') as interval from dual; 

INTERVAL     
--------------
24855 3:14:7.0

SQL> select numtodsinterval(power(2,31), 'SECOND') as interval from dual; 

SQL Error: ORA-01873: the leading precision of the interval is too small
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.

As an epoch that highest allowed number of second represents 2038-01-19 03:14:07. This is the year 2038 problem, essentially.

You can get there with a negative number too:

SQL> select numtodsinterval(-2208988800, 'SECOND') as interval from dual;

SQL Error: ORA-01873: the leading precision of the interval is too small

Using -power(2, 31) wraps to a positive value, but anything lower than that errors:

SQL> select numtodsinterval(power(2,31) - 1, 'SECOND') as interval from dual;

INTERVAL     
--------------
24855 3:14:7.0

SQL> select numtodsinterval(-power(2,31), 'SECOND') as interval from dual;

INTERVAL     
--------------
24855 3:14:8.0

SQL> select numtodsinterval(-power(2,31) - 1, 'SECOND') as interval from dual;

SQL Error: ORA-01873: the leading precision of the interval is too small

You are dividing by 1000, so one of your columns F to K has a value that exceeds 2147483647000. That should be fairly easy to find, and you might want to consider adding a check constraint to those columns so they can't be set too high - check that the column value is less than or equal to 1000 * (power(2, 31) - 1). And either greater than zero, or greater than-1000 * (power(2, 31) too.

The reason it doesn't error when you have a filter like where Col1 = 123 is that your filter (predicate) is pushed up into the view query and the row(s) with values that are too high are not evaluated. Perhaps you only have a single such value, and its col1 value is not 123 and its col2 value is not 'xyz'. If you identify a problem row and filter using it's actual col1 value it will still error. With no filters the evaluation is done for all rows.

---

The specific negative number you have seems to be a magic number:

SQL> select date '1970-01-01' - 2208988800/86400 from dual;

DATE'1970-01-01'-2208988800/86400
---------------------------------
1900-01-01 00:00:00              

If you want to exclude that then you would have modify the view definition to either add a filter, e.g:

...
AND tab2.colh > 0

or change the column expression to handle it, eithe rignoring it and leaving it null, or probably more usefully returning that magic date:

    TO_CHAR(CASE WHEN tab2.colh = -2208988800000 THEN DATE'1900-01-01'
      ELSE DATE'1970-01-01' + NUMTODSINTERVAL( tab2.colh / 1000,'SECOND')
      END, 'YYYY/MM/DD HH24:MI:SS') AS Col13,

You could also change from using an interval to using date arithmetic:

    TO_CHAR(DATE'1970-01-01' + ( tab2.colh / 86400000 ), 'YYYY/MM/DD HH24:MI:SS') AS Col13,

You'll have to modify the view definition rather than your query though, unless colh is included in the select list (which it doesn't seem to be), and even if it was you could only exclude it - and that still might not always avoid the error, depending on how the optimiser handled the query.