If you’re trying to use an interval literal in Oracle, but you keep getting the “leading precision of the interval is too small” error, hopefully this helps.

The Error

Here’s an example of the error:

SELECT INTERVAL '125' YEAR
FROM DUAL;

Result:

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.
Error at Line: 9 Column: 17

The Solution

Here’s how to fix the issue:

SELECT INTERVAL '125' YEAR(3)
FROM DUAL;

Result:

+125-00

All I did was append (3) to the YEAR keyword. This specifies a precision of 3.

The default precision is 2, and so if we don’t specify a higher precision, the error occurs.

You can provide a precision up to 9.

Example:

SELECT INTERVAL '123456789' YEAR(9)
FROM DUAL;

Result:

+123456789-00

And here’s what happens if we reduce the precision while keeping the number the same:

SELECT INTERVAL '123456789' YEAR(5)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT INTERVAL '123456789' YEAR(5)
FROM DUAL
Error at Command Line : 1 Column : 17
Error report -
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.

Same error as before.

Also, anything higher than 9 results in an error:

SELECT INTERVAL '123456789' YEAR(20)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT INTERVAL '123456789' YEAR(20)
FROM DUAL
Error at Command Line : 1 Column : 34
Error report -
SQL Error: ORA-30088: datetime/interval precision is out of range
30088. 00000 -  "datetime/interval precision is out of range"
*Cause:    The specified datetime/interval precision was not between 0 and 9.
*Action:   Use a value between 0 and 9 for datetime/interval precision.