Saturday, April 02, 2011

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. 



2 comments:

LewisC said...

Nice. I recently wrote a few custom aggregates having completely forgotten about collect.

The ones I did were considerably more complex than string aggregation but I think I will go back and see if I can simplify with collect.

Timely post. Thanks.

LewisC

Damir Vadas said...

Hi!

You do not deal "strange" unknown types. Maybe something her (http://damir-vadas.blogspot.com/2010/01/export-table-to-filecsv.html) may be a kind of your interest.

Rg,
Damir