create or replace type STRING_TOAGG_TYPE as object
(
-- Purpose : Passes parameters to the string_aggr_type's ODCIAggregateIterate function
-- Attributes
strvalue CLOB, -- varchar2(4000), --32767-plsql -CLOB --sql
separator varchar2(100)
-- Member functions and procedures
-- N.a.
)
/


create or replace type string_aggr_type as object (
 total CLOB , -- varchar2(4000),  --32767-plsql -CLOB --sql
static function ODCIAggregateInitialize(sctx IN OUT string_aggr_type )
 return number,
member function ODCIAggregateIterate(self IN OUT string_aggr_type ,
                                     value IN STRING_TOAGG_TYPE) return number,
member function ODCIAggregateTerminate(self IN string_aggr_type,
                                       returnValue OUT  CLOB, -- varchar2,
                                       flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT string_aggr_type,
                                   ctx2 IN string_aggr_type) return number
);
/
create or replace type body string_aggr_type is

  static function ODCIAggregateInitialize(sctx IN OUT string_aggr_type)
    return number is
  begin
    sctx := string_aggr_type(null);
    return odciconst.success;
  end;
member function ODCIAggregateIterate(self  IN OUT string_aggr_type,
                                     value IN STRING_TOAGG_TYPE) return number is
  begin
    self.total :=
     --можно целенаправленно резать, дабы не вылезать за    
     /* CASE WHEN length(self.total) + length(value.strvalue)+length(value.separator) >= 32767 THEN
        SUBSTR(self.total,0,32763)||'...' --*/  --32767-plsql -CLOB --sql
      /*CASE WHEN length(self.total) + length(value.strvalue)+length(value.separator) >= 4000 THEN
        SUBSTR(self.total,0,3996)||'...'*/
      --ELSE
        CASE WHEN value.strvalue IS NULL THEN self.total
          ELSE 
            CASE WHEN self.total IS NULL THEN NULL  ELSE self.total || value.separator END || value.strvalue
          END
      --END
      ;
    return odciconst.success;
   end;
member function ODCIAggregateTerminate(self        IN string_aggr_type,
                                       returnvalue OUT CLOB, -- varchar2,
                                       flags       IN number
                                      ) return number is
  begin
    returnvalue := self.total; -- ltrim(self.total, ',');
    return odciconst.success;
  end;
member function ODCIAggregateMerge(self IN OUT string_aggr_type,
                                   ctx2 IN string_aggr_type) return number is
  begin
    self.total := self.total || ctx2.total;
    return odciconst.success;
  end;
end;
/
CREATE or replace FUNCTION string_agg(input STRING_TOAGG_TYPE) RETURN CLOB --varchar2
  PARALLEL_ENABLE
  AGGREGATE USING string_aggr_type;
/

-- test
with t as
( select 'a_'||rownum val from dual
CONNECT BY rownum<8000
)
select string_agg(STRING_TOAGG_TYPE(val,'<br>')) as val
,length(string_agg(STRING_TOAGG_TYPE(val,'<br>'))) as length
from t;
--7.16second --length = 78879