/*String Aggregate Merge*/
set sqlblanklines on
create or replace type string_agg_type_clob as object
(
total clob,
static function
ODCIAggregateInitialize(sctx IN OUT string_agg_type_clob )
return number,
member function
ODCIAggregateIterate(self IN OUT string_agg_type_clob ,
value IN clob )
return number,
member function
ODCIAggregateTerminate(self IN string_agg_type_clob,
returnValue OUT clob,
flags IN number)
return number,
member function
ODCIAggregateMerge(self IN OUT string_agg_type_clob,
ctx2 IN string_agg_type_clob)
return number
);
/
create or replace type body string_agg_type_clob
is
static function ODCIAggregateInitialize(sctx IN OUT string_agg_type_clob)
return number
is
begin
sctx := string_agg_type_clob( null );
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT string_agg_type_clob,
value IN clob )
return number
is
begin
self.total := self.total ||/* ',' ||*/ value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN string_agg_type_clob,
returnValue OUT clob,
flags IN number)
return number
is
begin
--returnValue := ltrim(self.total,',');
returnValue := self.total;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT string_agg_type_clob,
ctx2 IN string_agg_type_clob)
return number
is
begin
self.total := self.total || ctx2.total;
return ODCIConst.Success;
end;
end;
/
CREATE or replace
FUNCTION straggc(input clob )
RETURN clob
PARALLEL_ENABLE AGGREGATE USING string_agg_type_clob;
/
set sqlblanklines off
- 1
- 2
前往页