From time to time Oracle seems to be limited in aggregate functions. However Oracle delivers possibility to create own aggregate function using built-in framework ODCIAggregate routines

Each of the four ODCIAggregate routines required to define a user-defined aggregate function codifies one of the internal operations that any aggregate function performs, namely:

• Initialize – ODCIAggregateInitialize – initialize the computation of the user-defined aggregate
• Iterate – ODCIAggregateIterate – calculates aggregations
• Merge – ODCIAggregateMerge – combines aggregation iterations
• Terminate – ODCIAggregateTerminate – retuns aggregation value

Let’s prepare test data

create table test_tbl
(
  id1 number,
  id2 number
);

begin
  for i in 1..2
  loop
    for j in 1..4
    loop
      insert into test_tbl values(i, j);
    end loop;
  end loop;
  
  commit;
end;
/

select * from test_tbl;

       ID1        ID2
---------- ----------
         1          1 
         1          2 
         1          3 
         1          4 
         2          1 
         2          2 
         2          3 
         2          4

Now it’s time to build own aggregate function secondmax that will find second maximum value

CREATE TYPE secondmaximpl AS OBJECT
(
  max1   NUMBER, -- highest value seen so far 
  max2   NUMBER, -- second highest value seen so far
  
  STATIC FUNCTION odciaggregateinitialize
  ( 
    sctx IN OUT secondmaximpl 
  ) 
  RETURN NUMBER,
  
  MEMBER FUNCTION odciaggregateiterate
  ( 
    self  IN OUT secondmaximpl, 
    VALUE IN     NUMBER
  ) RETURN NUMBER,
  
  MEMBER FUNCTION odciaggregateterminate
  ( 
    self        IN  secondmaximpl, 
    returnvalue OUT NUMBER, 
    flags       IN  NUMBER 
  ) RETURN NUMBER,

  MEMBER FUNCTION odciaggregatemerge
  ( 
    self IN OUT secondmaximpl, 
    ctx2 IN     secondmaximpl 
  ) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY secondmaximpl IS 
  STATIC FUNCTION odciaggregateinitialize
  (
    sctx IN OUT secondmaximpl
  ) 
  RETURN NUMBER IS 
  BEGIN
    sctx := secondmaximpl(0, 0);
    RETURN odciconst.success;
  END;

  MEMBER FUNCTION odciaggregateiterate
  (
    self  IN OUT secondmaximpl, 
    VALUE IN     NUMBER
  ) 
  RETURN NUMBER IS
  BEGIN
    IF VALUE > self.max1 THEN
      self.max2 := self.max1;
      self.max1 := VALUE;
    ELSIF VALUE > self.max2 THEN
      self.max2 := VALUE;
    END IF;
    
    RETURN odciconst.success;
  END;

  MEMBER FUNCTION odciaggregateterminate
  (
    self        IN  secondmaximpl, 
    returnvalue OUT NUMBER, 
    flags       IN  NUMBER
  ) 
  RETURN NUMBER IS
  BEGIN
    returnvalue := self.max2;
    RETURN odciconst.success;
  END;

  MEMBER FUNCTION odciaggregatemerge
  (
    self IN OUT secondmaximpl, 
    ctx2 IN     secondmaximpl
  ) 
  RETURN NUMBER IS
  BEGIN
    IF ctx2.max1 > self.max1 THEN
      IF ctx2.max2 > self.max2 THEN 
        self.max2 := ctx2.max2;
      ELSE
        self.max2 := self.max1;
      END IF;
      self.max1 := ctx2.max1;
    ELSIF ctx2.max1 > self.max2 THEN
      self.max2 := ctx2.max1;
    END IF;
    
    RETURN odciconst.success;
  END;
END;
/

CREATE OR REPLACE FUNCTION secondmax 
(
  input NUMBER
) 
RETURN NUMBER 
PARALLEL_ENABLE AGGREGATE USING secondmaximpl;
/

Test your new aggregate function

SELECT id1, secondmax(id2)
FROM test_tbl
GROUP BY id1;

       ID1 SECONDMAX(ID2)
---------- --------------
         1              3 
         2              3

Another example string aggregation example based on CLOB

CREATE OR REPLACE TYPE t_str_agg AS OBJECT
(
  big_string  CLOB,

  STATIC FUNCTION ODCIAggregateInitialize
  (
    sctx  IN OUT  t_str_agg
  )
  RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate
  (
    self   IN OUT  t_str_agg,
    value  IN      CLOB 
  )
  RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate
  (
    self         IN   t_str_agg,
    returnValue  OUT  CLOB,
    flags        IN   NUMBER
  )
  RETURN NUMBER,
  
  MEMBER FUNCTION ODCIAggregateMerge
  (
    self  IN OUT  t_str_agg,
    ctx2  IN      t_str_agg
  )
  RETURN NUMBER
);
/
SHOW ERRORS


CREATE OR REPLACE TYPE BODY t_str_agg IS
  STATIC FUNCTION ODCIAggregateInitialize
  (
    sctx  IN OUT  t_str_agg
  )
  RETURN NUMBER 
  IS
  BEGIN
    sctx := t_str_agg(NULL);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate
  (
    self   IN OUT  t_str_agg,
    value  IN      CLOB 
  )
  RETURN NUMBER IS
  BEGIN
    SELF.big_string := self.big_string || ',' || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate
  (
    self         IN   t_str_agg,
    returnValue  OUT  CLOB,
    flags        IN   NUMBER
  )
  RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.big_string, ','), ',');
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge
  (
    self  IN OUT  t_str_agg,
    ctx2  IN      t_str_agg
  )
  RETURN NUMBER IS
  BEGIN
    SELF.big_string := SELF.big_string || ',' || 
                       ctx2.big_string;
    RETURN ODCIConst.Success;
  END;
END;
/
SHOW ERRORS

CREATE OR REPLACE FUNCTION str_agg (p_input CLOB)
RETURN CLOB
PARALLEL_ENABLE AGGREGATE USING t_str_agg;
/
SHOW ERRORS

and output

SELECT id1, str_agg(to_char(id2))
FROM test_tbl
GROUP BY id1;

ID1  STR_AGG(TO_CHAR(ID2))
---  -----------------------
1    1,4,3,2
2    1,4,3,2

Have a fun 🙂

Tomasz