This 2nd version allows to concatenate strings and indicate how they're ordered.
Source code
create type tq84_listagg_rec authid definer as object (
value_ varchar2(4000),
order_by integer
);
/
create type tq84_listagg_rec_t as table of tq84_listagg_rec;
/
create or replace type tq84_listagg_t authid definer as object (
sep varchar2(4000),
recs tq84_listagg_rec_t,
static function ODCIAggregateInitialize(sctx in out nocopy tq84_listagg_t,
separator varchar2 )
return number,
member function ODCIAggregateIterate (self in out nocopy tq84_listagg_t,
rec in tq84_listagg_rec
-- value_ in varchar2 ,
-- order_by in integer
)
return number,
member function ODCIAggregateTerminate (self in out nocopy tq84_listagg_t,
return_value out nocopy clob ,
flags in number )
return number,
member function ODCIAggregateMerge (self in out nocopy tq84_listagg_t,
ctx2 in tq84_listagg_t)
return number,
static function go(rec tq84_listagg_rec, order_by number)
return clob
-- parallel_enable
aggregate using tq84_listagg_t
);
/
create or replace type body tq84_listagg_t is
static function ODCIAggregateInitialize(sctx in out nocopy tq84_listagg_t, separator varchar2)
return number
is
begin
sctx := tq84_listagg_t(separator, tq84_listagg_rec_t());
return ODCIConst.success;
end ODCIAggregateInitialize;
member function ODCIAggregateIterate(
self in out nocopy tq84_listagg_t,
rec in tq84_listagg_rec
)
return number is
begin
self.recs.extend;
self.recs(self.recs.count) := rec;
return ODCIConst.success;
end ODCIAggregateIterate;
member function ODCIAggregateTerminate(
self in out nocopy tq84_listagg_t,
return_value out nocopy clob,
flags in number
)
return number is
first_ boolean := true;
begin
dbms_lob.createTemporary(return_value, true, dbms_lob.call);
for x in (select value_ from table(self.recs) order by order_by) loop
if first_ then
first_ := false;
else
dbms_lob.writeAppend(return_value, length(self.sep), self.sep);
end if;
dbms_lob.writeAppend(return_value, length(x.value_), x.value_);
end loop;
-- TODO: who is responsible for freeing the clob
-- dbms_lob.freeTemporary(return_value);
return ODCIConst.success;
end ODCIAggregateTerminate;
member function ODCIAggregateMerge(
self in out nocopy tq84_listagg_t,
ctx2 in tq84_listagg_t
) return number is
aggregated clob;
begin
raise_application_error(-20800, 'is the method ever called without parallel mode?');
/*
if ctx2.aggregated is not null then
dbms_lob.writeAppend(self.aggregated, length(sep) , sep );
dbms_lob.append (self.aggregated, ctx2.aggregated);
end if;
*/
return ODCIConst.success;
end ODCIAggregateMerge;
end;
/
create or replace function tq84_listagg_f(
rec tq84_listagg_rec,
separator varchar2
)
return clob authid definer
-- parallel_enable
aggregate using tq84_listagg_t;
/
Test
select
tq84_listagg_f(tq84_listagg_rec(object_name, object_id), ' - ')
from
user_objects
;
select
tq84_listagg_f(tq84_listagg_rec(column_name, -column_id), ',')
from
user_tab_columns
;