A new variant on stragg - the ordered aggregate

In a previous post, I included an example of a variant of Tom Kyte's STRAGG (string aggregate) function that ordered the elements alphabetically. Here I'm showing one where the elements can be ordered differently. Basically, for every string to be aggregated, you need to pass in the value itself and its order position. It doesn't test to see if that order position is already taken, so if you have two values in sort position 3, one of them will be lost. Also, the order position should be an integer.

Firstly, create the necessary types. The first two are pretty obvious, the third is the aggregate type:

create or replace type sort_element is object
   (sort_seq number, sort_value varchar2(2000))
.
/

create type tab_sort is table of sort_element
.
/

create or replace type str_ord_agg_type as object
(
   t_sort tab_sort,
   static function
        ODCIAggregateInitialize(sctx IN OUT str_ord_agg_type )
        return number,
   member function
        ODCIAggregateIterate(self IN OUT str_ord_agg_type ,
                             value IN sort_element )
        return number,
   member function
        ODCIAggregateTerminate(self IN str_ord_agg_type,
                               returnValue OUT  varchar2,
                               flags IN number)
        return number,
   member function
        ODCIAggregateMerge(self IN OUT str_ord_agg_type,
                           ctx2 IN str_ord_agg_type)
        return number,
   member function
        ODCIAggregateDelete(self IN OUT str_ord_agg_type,
                            value in sort_element)
        return number
)
.
/

Then the type body for the aggregation operation

create or replace type body str_ord_agg_type
is
--
static function ODCIAggregateInitialize
    (sctx IN OUT str_ord_agg_type)
return number
is
begin
    sctx := str_ord_agg_type( null );
    --Initialize the sortage part
    sctx.t_sort := tab_sort();
    return ODCIConst.Success;
end;
--
member function ODCIAggregateIterate
     (self IN OUT str_ord_agg_type,
      value IN sort_element )
return number
is
begin
    --Extend the array and add the new element.
    t_sort.extend;
    t_sort(t_sort.last) := value;
    return ODCIConst.Success;
end;
--
member function ODCIAggregateTerminate
          (self IN str_ord_agg_type,
           returnValue OUT varchar2,
           flags IN number)
return number
is
  type tab_char is table of varchar2(4000) index by pls_integer;
  t_char tab_char;
  v_ind number;
  v_out_line varchar2(4000);
begin
  --Store the elements in the array using the sort seq
  v_ind := t_sort.first;
  while v_ind is not null loop
    t_char(t_sort(v_ind).sort_seq) := t_sort(v_ind).sort_value;
    v_ind := t_sort.next(v_ind);
  end loop;
  --
  --Go through in sort seq order, moving elements to the output string
  v_ind := t_char.first;
  while v_ind is not null loop
    v_out_line := v_out_line||t_char(v_ind)||',';
    v_ind := t_char.next(v_ind);
  end loop;
  --Remove last comma
  v_ind := length(nvl(v_out_line,',')) -1;
  returnValue := (substr(v_out_line,1,v_ind));
  return ODCIConst.Success;
end;
--
member function ODCIAggregateDelete
      (self IN OUT str_ord_agg_type,
       value in sort_element)
return number
is
  v_point number;
begin
  v_point := t_sort.first;
  while v_point is not null loop
    if  t_sort(v_point).sort_value = value.sort_value
    and t_sort(v_point).sort_seq = value.sort_seq then
      t_sort.delete(v_point);
      return ODCIConst.Success;
    end if;
    v_point := t_sort.next(v_point);
  end loop;
  return ODCIConst.Success;
end;
--
member function ODCIAggregateMerge
         (self IN OUT str_ord_agg_type,
           ctx2 IN str_ord_agg_type)
return number
is
  v_point number;
begin
  v_point := ctx2.t_sort.first;
  while v_point is not null loop
    t_sort.extend;
    t_sort(t_sort.last) := ctx2.t_sort(v_point);
    v_point := ctx2.t_sort.next(v_point);
  end loop;
  return ODCIConst.Success;
end;
end;
.
/

And finally the function :

CREATE or replace
  FUNCTION strordagg(input sort_element )
  RETURN varchar2
  PARALLEL_ENABLE AGGREGATE USING str_ord_agg_type;
.
/

grant execute on strordagg to public;

select table_name, index_name,
       strordagg(sort_element(column_position, column_name)) sorted
from user_ind_columns
group by table_name, index_name
/

In 10G, we can use the COLLECT function using the same sort_element and tab_sort types, but with a much simpler function, though the select looks a little uglier.

create or replace function sorted_agg
    (p_tab_sort in tab_sort) return varchar2 is
  type tab_char is table of varchar2(4000) index by pls_integer;
  t_char tab_char;
  v_ind number;
  v_out_line varchar2(4000);
begin
  v_ind := p_tab_sort.first;
  while v_ind is not null loop
    t_char(p_tab_sort(v_ind).sort_seq) := p_tab_sort(v_ind).sort_value;
    v_ind := p_tab_sort.next(v_ind);
  end loop;
  --
  v_ind := t_char.first;
  while v_ind is not null loop
    v_out_line := v_out_line||t_char(v_ind)||',';
    v_ind := t_char.next(v_ind);
  end loop;
  --Remove last comma
  v_ind := length(v_out_line) -1;
  return(substr(v_out_line,1,v_ind));
end;
.
/

select index_name,
       sorted_agg(cast(collect(sort_element(column_position, column_name)) as tab_sort)) sorted
from all_ind_columns
group by index_name
/