CREATE OR REPLACE TYPE string_agg_vch2_tab as table of varchar2(2000);

CREATE OR REPLACE TYPE string_agg_type as object
(
       coll string_agg_vch2_tab,
       static function
            ODCIAggregateInitialize(sctx IN OUT string_agg_type )
            return number,
       member function
           ODCIAggregateIterate(self IN OUT string_agg_type ,
                                value IN varchar2 )
           return number,
       member function
           ODCIAggregateTerminate(self IN string_agg_type,
                                  returnValue OUT  clob, --varchar2,
                                  flags IN number)
           return number,
       member function
           ODCIAggregateMerge(self IN OUT string_agg_type,
                              ctx2 IN string_agg_type)
           return number
)
/

CREATE OR REPLACE TYPE BODY string_agg_type
is
    static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
    return number
    is
    begin
        sctx := string_agg_type( string_agg_vch2_tab() );
        return ODCIConst.Success;
   end;
   member function ODCIAggregateIterate(self IN OUT string_agg_type,
                                        value IN varchar2)
   return number
   is
   begin
       self.coll.extend;
       self.coll(self.coll.count) := value;
       return ODCIConst.Success;
   end;
   member function ODCIAggregateTerminate(self IN string_agg_type,
                                          returnValue OUT clob, --varchar2,
                                          flags IN number)
   return number
   is
     type ltt is table of int index by varchar2(2000);
     lt ltt;
     idx varchar2(2000);
   begin
       -- зело ленив я сортировку писать.
       for i in 1..self.coll.count() loop
         if lt.exists(self.coll(i)) then
           lt(self.coll(i)) := lt(self.coll(i)) + 1;
         else
           lt(self.coll(i)) := 1;
         end if;
       end loop;

       idx := lt.first;
       while idx is not null loop
         for i in 1..lt(idx) loop
           returnValue := case when returnValue is not null then returnValue||','||idx else idx end;
         end loop;
         idx := lt.next(idx);
       end loop;

       return ODCIConst.Success;
   end;
   member function ODCIAggregateMerge(self IN OUT string_agg_type,
                                      ctx2 IN string_agg_type)
   return number
   is
   begin
       self.coll := self.coll multiset union all ctx2.coll;
       return ODCIConst.Success;
   end;
end;
/

CREATE OR REPLACE FUNCTION GROUP_CONCAT(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/