How COLLECT displaced User Defined Aggregates

A recent PL/SQL Challenge question focussed on a user defined aggregate. Not a lot of people got it right, and one reason is that they have never been particularly popular. Tom Kyte's STRAGG function (dating back to 2001) was probably the best known usage. But they are basically complicated and a bit ugly.


When 10g came along, it added the COLLECT aggregate function. That meant that the old style user defined aggregates were pretty much redundant. It is a lot easier to aggregate up using COLLECT and then pass the collection to a regular function. 


It is also more flexible. As an example, since the user-defined aggregates could only have one parameter, it wasn't possible to pass a delimiter to the STRAGG function. This implementation, based on a 'regular' function overcomes that:



CREATE OR REPLACE TYPE TAB_CHAR IS table of VARCHAR2(4000);
/


create or replace function coll_to_delimited 
  (i_coll_type in tab_char, i_delim in varchar default ',')
  return varchar2 
is
  v_ret varchar2(32767);
begin
  FOR i IN 1..i_coll_type.count LOOP
    IF instr(i_coll_type(i),i_delim) > 0 THEN
      RAISE_APPLICATION_ERROR(-20001,
          'Delimiter present in string:'||i_coll_type(i));
    END IF;
    v_ret := v_ret || i_coll_type(i);
    IF i != i_coll_type.count THEN
      v_ret := v_ret||i_delim;
    END IF;
  END LOOP;
  RETURN v_ret;
end;
/

select coll_to_delimited(cast(collect(table_name)
                             as tab_char),'|') 
from user_tables;

I've even included a check to indicate where a string already includes the delimiter. I don't bother to include a specific check on length as that would be an error anyway.