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

Last updated on AUGUST 27, 2013

Applies to:

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


Goal

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

e.g.

create or replace type SUM_INTV as object (
  INTV_SUM              interval day(9) to second(9),
  IN_DEBUG              varchar2(1),
  static function ODCIAGGREGATEINITIALIZE(SCTX in out SUM_INTV)
    return number,
  member function ODCIAGGREGATEITERATE(
    self        in out  SUM_INTV,
    value       in      interval day to second
  ) return number,
  member function ODCIAGGREGATETERMINATE(
    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


end;
/


create or replace function SUMINTVAGG(INPUT interval day to second)
  return interval day to second
  parallel_enable
  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));


COMMIT;

set echo on


select SUMINTVAGG(O.CRE_DUR)
            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;

select SUMINTVAGG(O.CRE_DUR)
*
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:
to



Solution

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