User defined aggregate functions in Oracle 9i

create or replace type agg_t as object (

    str_agg varchar2(4000),

    static function ODCIAggregateInitialize(sctx  in out agg_t) 
                    return number,

    member function ODCIAggregateIterate   (self  in out agg_t, 
                                            value in varchar2 ) 
                    return number,

    member function ODCIAggregateTerminate (self         in     agg_t   , 
                                            return_value    out varchar2, 
                                            flags        in number      )               
                    return number,

    member function ODCIAggregateMerge(self in out agg_t, 
                                       ctx2 in agg_t    ) 
                    return number
);
/

create or replace type body agg_t is 

    static function ODCIAggregateInitialize(sctx in out agg_t) 
        return number is 
    begin
        sctx := agg_t(null);
        return ODCIConst.Success;
    end;

    member function ODCIAggregateIterate(
      self in out agg_t, value in varchar2) 
        return number is
    begin
        str_agg := str_agg || value;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateTerminate(self in agg_t, 
        return_value out varchar2, flags in number) return number is
    begin
        return_value := str_agg;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateMerge(self in out agg_t, 
        ctx2 in agg_t) return number is
    begin
        str_agg := str_agg || ctx2.str_agg;
        return ODCIConst.Success;
    end;
end;
/

create or replace function agg_concat (input varchar2) return varchar2
    parallel_enable aggregate using agg_t;
/

create table agg_test (
  a number,
  b varchar2(5)
);

insert into agg_test values (1,'a');
insert into agg_test values (2,'A');
insert into agg_test values (1,'b');
insert into agg_test values (1,'c');
insert into agg_test values (2,'B');
insert into agg_test values (2,'C');
insert into agg_test values (1,'d');
insert into agg_test values (2,'D');

column b_conc format a30
select
  a, agg_concat(b) b_conc from agg_test 
group by
  a;

         A B_CONC
---------- ------------------------------
         1 abcd
         2 ABCD

drop table agg_test;
drop function agg_concat;
drop type agg_t;