An aggregate function for collection types

Today I stumbled upon a blog entry where the poster needs something like Tom Kyte's stragg function, only with dynamic delimiters. Here's my solution, using a variation of stragg that accumulates data in a collection instead of appending it to a string.

First of all, this is how you use it:

SQL> r
1  with data as (
2  select lpad('x',level,'x') data
3  from dual
4  connect by level <= 5)
5  select v2_pkg.join(v2_agg(data),'-')
6* from data

 V2_PKG.JOIN(V2_AGG(DATA),'-')
 --------------------------------------------------------------------------------
 x-xx-xxx-xxxx-xxxxx

 1 Zeile wurde ausgewählt.

The v2_agg function (and it's underlying object type) append strings to a collection. The v2_pkg.join concatenates the elements of it's first argument (the collection) with the second argument in between. See below for the source code. It's part of my everyday toolkit, v2_pkg contains lots more, for example.

The collection type

create type v2_tbl as table of varchar2(4000);

The aggregate object type spec

create or replace type v2_agg_type as object (                                  
  elements v2_tbl,
  static function
    ODCIAggregateInitialize(sctx IN OUT v2_agg_type )
    return number,
  member function
    ODCIAggregateIterate(self IN OUT v2_agg_type ,
                         value IN varchar2 )
    return number,
  member function
    ODCIAggregateTerminate(self IN v2_agg_type,
                           returnValue OUT  v2_tbl,
                           flags IN number)
    return number,
  member function
    ODCIAggregateMerge(self IN OUT v2_agg_type,
                       ctx2 IN v2_agg_type)
    return number
);                                       
/

The aggregate function

create or replace function v2_agg(input varchar2)
return v2_tbl                                                                 
parallel_enable aggregate using v2_agg_type;
/

The utility package spec

create or replace package v2_pkg as
  function join(i_tbl in v2_tbl,
                i_glue in varchar2 := ',')
  return varchar2;
end;
/

The object type body

create or replace type body v2_agg_type  is
  static function ODCIAggregateInitialize(sctx IN OUT v2_agg_type)
  return number
  is
  begin
    sctx := v2_agg_type( null );
    return ODCIConst.Success;
  end;
  member function ODCIAggregateIterate(self IN OUT v2_agg_type,
                                       value IN varchar2 )
  return number
  is
  begin
    if self.elements is null then
      self.elements := v2_tbl();
    end if;
    self.elements.extend;
    self.elements(self.elements.count) := value;
    return ODCIConst.Success;
  end;
  member function ODCIAggregateTerminate(self IN v2_agg_type,
                                         returnValue OUT v2_tbl,
                                         flags IN number)
  return number
  is
  begin
    returnValue := self.elements;
    return ODCIConst.Success;
  end;
  member function ODCIAggregateMerge(self IN OUT v2_agg_type,
                                     ctx2 IN v2_agg_type)
  return number
  is
  begin
    if ctx2.elements is not null then
      if self.elements is null then
        self.elements := v2_tbl();
      end if;
      for i in 1 .. ctx2.elements.count loop
        self.elements.extend;
        self.elements(self.elements.count) := ctx2.elements(i);
      end loop;
    end if;
    return ODCIConst.Success;
  end;
end;
/

The utility package body

create or replace package body v2_pkg as
   function join(i_tbl  in v2_tbl,
                 i_glue in varchar2 := ',')
  return varchar2
  is
    v_str varchar2(32767);
  begin
    IF i_tbl is not null THEN
      FOR i in 1 .. i_tbl.count LOOP
        v_str := v_str || i_glue || i_tbl(i);
      END LOOP;
    END IF;
    return substr(v_str,length(i_glue)+1);
  end;
end;
/

By the way, Oracle's COLLECT function might be more appropriate than v2_agg for large datasets. This post explains why.