ORA-1873 : When Passing Datetime Interval with Precision > 2 in a Parameter (Doc ID 394260.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: 9.0.1.0 to 11.1.0.6 - Release: 9.0.1 to 11.1
Information in this document applies to any platform.
Checked for relevance on 23-Apr-2010

Symptoms

An INTERVAL type passed as a parameter to a procedure or function causes ORA-01873 when the precision is larger then the default precision of 2.  The issue is that it is not possible to specify the allowed maximum precision (9) only a precision up to 2, so any precision larger then this causes the error.    

For example, a procedure with an INTERVAL YEAR TO MONTH parameter, fails when the precision is greater then the default:

CREATE OR REPLACE PROCEDURE intervalproc (p_inter IN OUT INTERVAL YEAR TO MONTH) IS
BEGIN
  p_inter := NUMTOYMINTERVAL(100, 'YEAR');
END intervalproc;
/

DECLARE 
  inter INTERVAL YEAR(4) TO MONTH;
BEGIN
  intervalproc(inter);
END;
/

ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
ORA-06512: at "SCOTT.INTERVALPROC", line 3
ORA-06512: at line 4

Also, a function which attempts to return an INTERVAL DAY TO SECOND, fails when the precision is greater then 2.

CREATE OR REPLACE FUNCTION fn (arg INTERVAL DAY TO SECOND) 
RETURN INTERVAL DAY TO SECOND IS
BEGIN 
  return (arg); 
END;
/


SELECT fn(to_dsinterval('100 10:00:00')) FROM dual;

 

ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
ORA-06512: at "SCOTT.FN", line 3

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms