This shows how to collect values from multiple rows into a single, comma delimited string. This is also known as "string aggregation".
I need to create this on "nddba" schema.
--------------------------- Comma Separated ----------------------------------------------
--========================================================
CREATE OR REPLACE TYPE nddba.stragg_type as object (
string varchar2 (4000),
static function ODCIAggregateInitialize
( sctx in out stragg_type )
return number ,
member function ODCIAggregateIterate
( self in out stragg_type ,
value in varchar2
) return number ,
member function ODCIAggregateTerminate
( self in stragg_type,
returnvalue out varchar2,
flags in number
) return number ,
member function ODCIAggregateMerge
( self in out stragg_type,
ctx2 in stragg_type
) return number
);
/
CREATE OR REPLACE TYPE body nddba.stragg_type
is
static function ODCIAggregateInitialize
( sctx in out stragg_type )
return number
is
begin
sctx := stragg_type( null ) ;
return ODCIConst.Success ;
end;
member function ODCIAggregateIterate
( self in out stragg_type ,
value in varchar2
) return number
is
begin
self.string := self.string || ',' || value ;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate
( self in stragg_type ,
returnvalue out varchar2 ,
flags in number
) return number
is
begin
returnValue := ltrim( self.string, ',' );
return ODCIConst.Success;
end;
member function ODCIAggregateMerge
( self in out stragg_type ,
ctx2 in stragg_type
) return number
is
begin
self.string := self.string || ctx2.string;
return ODCIConst.Success;
end;
end;
/
CREATE OR REPLACE FUNCTION nddba.stragg
(input varchar2 )
return varchar2
deterministic
parallel_enable
aggregate using stragg_type
;
/
-------------------------------- 'br/' Separated ----------------------------------------------
--=======================================================
Step-1: Create STRAGG Type
CREATE OR REPLACE TYPE ndstate7.stragg_type as object
(
string varchar2(4000),
static function ODCIAggregateInitialize
( sctx in out stragg_type )
return number ,
member function ODCIAggregateIterate
( self in out stragg_type ,
value in varchar2
) return number ,
member function ODCIAggregateTerminate
( self in stragg_type,
returnvalue out varchar2,
flags in number
) return number ,
member function ODCIAggregateMerge
( self in out stragg_type,
ctx2 in stragg_type
) return number
);
/
Step-2: BODY
CREATE OR REPLACE TYPE BODY stragg_type IS STATIC FUNCTION ODCIAggregateInitialize ( sctx IN OUT stragg_type )
RETURN NUMBER IS
BEGIN
sctx := stragg_type ( NULL );
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate ( self IN OUT stragg_type,value IN VARCHAR2 )
RETURN NUMBER IS
BEGIN
self.string := self.string || 'br/' || value;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate ( self IN stragg_type,returnvalue OUT VARCHAR2,flags IN NUMBER )
RETURN NUMBER IS
BEGIN
returnValue := ltrim ( self.string,'br/' );
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge ( self IN OUT stragg_type,ctx2 IN stragg_type )
RETURN NUMBER IS
BEGIN
self.string := self.string || ctx2.string;
RETURN ODCIConst.Success;
END;
END;
/
Step-3: Create STRAGG Function
CREATE OR REPLACE FUNCTION ndstate.stragg
( input varchar2 )
return varchar2
deterministic
parallel_enable
aggregate using stragg_type
;
/
VVI: Have to add <> with all br/, in the single-quote.
