First of all, this is how you use it:
SQL> r
1 with data as (
2 select lpad('x',level,'x') data
3 from dual
4 connect by level <= 5)
5 select v2_pkg.join(v2_agg(data),'-')
6* from data
V2_PKG.JOIN(V2_AGG(DATA),'-')
--------------------------------------------------------------------------------
x-xx-xxx-xxxx-xxxxx
1 Zeile wurde ausgewählt.
The v2_agg function (and it's underlying object type) append strings to a collection. The v2_pkg.join concatenates the elements of it's first argument (the collection) with the second argument in between. See below for the source code. It's part of my everyday toolkit, v2_pkg contains lots more, for example.
The collection type
create type v2_tbl as table of varchar2(4000);
The aggregate object type spec
create or replace type v2_agg_type as object (
elements v2_tbl,
static function
ODCIAggregateInitialize(sctx IN OUT v2_agg_type )
return number,
member function
ODCIAggregateIterate(self IN OUT v2_agg_type ,
value IN varchar2 )
return number,
member function
ODCIAggregateTerminate(self IN v2_agg_type,
returnValue OUT v2_tbl,
flags IN number)
return number,
member function
ODCIAggregateMerge(self IN OUT v2_agg_type,
ctx2 IN v2_agg_type)
return number
);
/
The aggregate function
create or replace function v2_agg(input varchar2)
return v2_tbl
parallel_enable aggregate using v2_agg_type;
/
The utility package spec
create or replace package v2_pkg as
function join(i_tbl in v2_tbl,
i_glue in varchar2 := ',')
return varchar2;
end;
/
The object type body
create or replace type body v2_agg_type is
static function ODCIAggregateInitialize(sctx IN OUT v2_agg_type)
return number
is
begin
sctx := v2_agg_type( null );
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT v2_agg_type,
value IN varchar2 )
return number
is
begin
if self.elements is null then
self.elements := v2_tbl();
end if;
self.elements.extend;
self.elements(self.elements.count) := value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN v2_agg_type,
returnValue OUT v2_tbl,
flags IN number)
return number
is
begin
returnValue := self.elements;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT v2_agg_type,
ctx2 IN v2_agg_type)
return number
is
begin
if ctx2.elements is not null then
if self.elements is null then
self.elements := v2_tbl();
end if;
for i in 1 .. ctx2.elements.count loop
self.elements.extend;
self.elements(self.elements.count) := ctx2.elements(i);
end loop;
end if;
return ODCIConst.Success;
end;
end;
/
The utility package body
create or replace package body v2_pkg as
function join(i_tbl in v2_tbl,
i_glue in varchar2 := ',')
return varchar2
is
v_str varchar2(32767);
begin
IF i_tbl is not null THEN
FOR i in 1 .. i_tbl.count LOOP
v_str := v_str || i_glue || i_tbl(i);
END LOOP;
END IF;
return substr(v_str,length(i_glue)+1);
end;
end;
/
By the way, Oracle's COLLECT function might be more appropriate than v2_agg for large datasets. This post explains why.
