CREATE OR REPLACE TYPE sum_interval AS OBJECT
(

/* type implements oracles data cartridge interface to compute sum of day to second interval values */

   duration INTERVAL DAY TO SECOND, --duration accumulator

   CONSTRUCTOR FUNCTION sum_interval RETURN SELF AS RESULT
      PARALLEL_ENABLE,

   STATIC FUNCTION odciaggregateinitialize(ctx IN OUT sum_interval) RETURN NUMBER,

   MEMBER FUNCTION odciaggregateiterate
   (
      SELF  IN OUT sum_interval,
      VALUE IN INTERVAL DAY TO SECOND
   ) RETURN NUMBER,

   MEMBER FUNCTION odciaggregatedelete
   (
      SELF  IN OUT sum_interval,
      VALUE IN INTERVAL DAY TO SECOND
   ) RETURN NUMBER,

   MEMBER FUNCTION odciaggregateterminate
   (
      SELF        IN sum_interval,
      returnvalue OUT INTERVAL DAY TO SECOND,
      flags       IN NUMBER
   ) RETURN NUMBER,

   MEMBER FUNCTION odciaggregatemerge
   (
      SELF IN OUT sum_interval,
      ctx  IN sum_interval
   ) RETURN NUMBER
)
;
/

CREATE OR REPLACE TYPE BODY sum_interval IS

   ------------------------------------------------------------------------------------------------- 
   CONSTRUCTOR FUNCTION sum_interval RETURN SELF AS RESULT
      PARALLEL_ENABLE IS
   BEGIN
      RETURN;
   END sum_interval;
   
   ------------------------------------------------------------------------------------------------- 
   STATIC FUNCTION odciaggregateinitialize(ctx IN OUT sum_interval) RETURN NUMBER IS
   BEGIN
      ctx := sum_interval();
      RETURN odciconst.success;
   END odciaggregateinitialize;
   
   ------------------------------------------------------------------------------------------------- 
   MEMBER FUNCTION odciaggregateiterate
   (
      SELF  IN OUT sum_interval,
      VALUE IN INTERVAL DAY TO SECOND
   ) RETURN NUMBER IS
   BEGIN
      IF SELF.duration IS NULL THEN
         SELF.duration := VALUE;
      ELSE
         SELF.duration := SELF.duration + VALUE;
      END IF;
      RETURN odciconst.success;
   END odciaggregateiterate;
   
   ------------------------------------------------------------------------------------------------- 
   MEMBER FUNCTION odciaggregateterminate
   (
      SELF        IN sum_interval,
      returnvalue OUT INTERVAL DAY TO SECOND,
      flags       IN NUMBER
   ) RETURN NUMBER IS
   BEGIN
      returnvalue := SELF.duration;
      RETURN odciconst.success;
   END odciaggregateterminate;
   
   ------------------------------------------------------------------------------------------------- 
   MEMBER FUNCTION odciaggregatedelete
   (
      SELF  IN OUT sum_interval,
      VALUE IN INTERVAL DAY TO SECOND
   ) RETURN NUMBER IS
   BEGIN
      SELF.duration := SELF.duration - VALUE;
      RETURN odciconst.success;
   END odciaggregatedelete;
   
   ------------------------------------------------------------------------------------------------- 
   MEMBER FUNCTION odciaggregatemerge
   (
      SELF IN OUT sum_interval,
      ctx  IN sum_interval
   ) RETURN NUMBER IS
   BEGIN
      IF ctx.duration IS NULL THEN
         NULL;
      ELSE
         SELF.duration := SELF.duration + ctx.duration;
      END IF;
      RETURN odciconst.success;
   END odciaggregatemerge;
   
END;
/

create or replace function fce_sum_interval(duration interval day to second) return interval day to second
  parallel_enable
  aggregate using sum_interval;
/

select fce_sum_interval(numtodsinterval(level, 'minute')) from dual connect by level <= 10;