-- Simple aggregate function which concatenates its string
-- arguments into a single comma-separated string
--
-- Daylight CIS, 9/15/2005

-- Define object type and methods

create or replace type cagg_t as object (
    str_agg varchar2(4000),

    static function ODCIAggregateInitialize(sctx in out cagg_t) 
        return number,
    member function ODCIAggregateIterate(self in out cagg_t, 
        value in varchar2) return number,
    member function ODCIAggregateTerminate(self in cagg_t, 
        return_value out varchar2, flags in number) return number,
    member function ODCIAggregateMerge(self in out cagg_t, 
        ctx2 in cagg_t) return number
);
/

-- Implement methods

create or replace type body cagg_t is 

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

    member function ODCIAggregateIterate(
      self in out cagg_t, value in varchar2) 
        return number is
    begin
      if (length(str_agg) != 0) then
          str_agg := str_agg || ',' || value;
        else
          str_agg := value;
        end if;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateTerminate(self in cagg_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 cagg_t, 
        ctx2 in cagg_t) return number is
    begin
        str_agg := str_agg || ',' || ctx2.str_agg;
        return ODCIConst.Success;
    end;
end;
/

-- Define PL/SQL function which uses the new object type

create or replace function comma_concat (input varchar2) return varchar2
    parallel_enable aggregate using cagg_t;
/