Multi-line merge aggregate functions in Oracle

In an Oracle database, you can use the Wm_concat (column) function for field merging, but this method is not recommended by Oracle because the Wmsys user is used by the workspace Manager, whose function objects may differ from version to exception. This change is reflected in 11.2.0.3 and 10.2.0.5. Originally the WM_CONCAT function returned a value of VARCHAR2, but in some versions it becomes more CLOB. This change has resulted in many exceptions to the program. Instead of using this intrinsic function of workspace, Oracle recommends that users use custom functions to implement this functionality. This function contains a type, type Body, and function, which can be implemented by reference to the implementation of Oracle.

country              city
----------------------------------------
China                  Taipei
China          & nbsp;      Hong Kong
China                  Shanghai
Japan             & nbsp;   Tokyo
Japan                  Osaka

Country City
-------------------- --------------------
China Taipei Hong Kong Shanghai
Osaka, Tokyo, Japan

Select Country,strcat from T_city GROUP by country

1. Create a Type

Create or replace type Strcat_type as Object (
cat_string varchar2 (4000),
static function Odciaggregateinitialize (Cs_ctx in Out Strcat_type) return number,
member function odciaggregateiterate (self on Out strcat_type,value in VARCHAR2) return
Number
member function Odciaggregatemerge (self in Out strcat_type,ctx2 on out Strcat_type)
return number,
member function odciaggregateterminate (self on out strcat_type,returnvalue out
Varchar2,flags in number) return number
);

2. Create a type body

create or replace type body strcat_type is
static function Odciaggregateinitialize (cs_ctx in Out Strcat_type) return number
IS
BEGIN
      cs_ctx: = Strcat_type (null);
      return odciconst.success;
end;

member function odciaggregateiterate (self in Out strcat_type,
Value in VARCHAR2)
return number
Is
Begin
Self.cat_string: = self.cat_string | | '; ' | | Value
return odciconst.success;
End

member function odciaggregateterminate (self in Out strcat_type,
ReturnValue out VARCHAR2,
Flags in number)
return number
Is
Begin
ReturnValue: = LTrim (RTrim (self.cat_string, '; '), '; ');
return odciconst.success;
End

member function Odciaggregatemerge (self in Out strcat_type,
Ctx2 in Out Strcat_type)
return number
Is
Begin
Self.cat_string: = self.cat_string | | '; ' | | ctx2.cat_string;
return odciconst.success;
End

End

3. Create a function
CREATE OR REPLACE FUNCTION strcat (input varchar2)
RETURN VARCHAR2
Parallel_enable AGGREGATE USING Strcat_type;

Multi-line merge aggregate functions in Oracle