How To avoid ORA-1873 using aggregate functions and interval day(9) to second(9)

(Doc ID 727099.1)

Last updated on AUGUST 29, 2017

Applies to:

PL/SQL - Version to [Release 9.2 to 11.1]
Information in this document applies to any platform.
***Checked for relevance on 27-Aug-2017***


Defining an aggregate function based on interval day(9) to second(9) will fail at runtime with the following error:

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


create or replace type SUM_INTV as object (
  INTV_SUM              interval day(9) to second(9),
  IN_DEBUG              varchar2(1),
    return number,
    self        in out  SUM_INTV,
    value       in      interval day to second
  ) return number,
    self        in      SUM_INTV,
    RETURNVALUE out     interval day to second,
    FLAGS       in      number
  ) return number,
  member function ODCIAGGREGATEMERGE(
    self        in out  SUM_INTV,
    CTX2        in      SUM_INTV
  ) return number

create or replace type body SUM_INTV is

-- Implementation code omitted - see the later example in this article


create or replace function SUMINTVAGG(INPUT interval day to second)
  return interval day to second
  aggregate using SUM_INTV;

create table objlist (
  object_id number primary key,
  grp_id number not null,
  cre_dur interval day (9) to second (9));


set echo on

            from   OBJLIST O
            where  O.GRP_ID = 5;

This fails with:

SQL> select SUMINTVAGG(O.CRE_DUR) from OBJLIST O where O.GRP_ID = 5;

ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
ORA-06512: at line 1

The error is correct  because the default precision is 2 i.e. interval day to second is equivalent to interval day(2) to second(2).

If you try and modify the definitions of the parameters declared as interval day to second to interval day(9) to second(9) then you receive compilation errors because PL/SQL syntax does not allow it: e.g

ERROR at line 1:
ORA-06545: PL/SQL: compilation error - compilation aborted
ORA-06550: line 8, column 37:
PLS-00103: Encountered the symbol "(" when expecting one of the following:


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