Wm_concat characters more than 4000 of the processing method

When using Wm_concat or custom aggregation functions to make a string, you may encounter a result set that is larger than 4000, and the system will be prompted to exceed the system limit. So, at this point, the best way is to process the result set into the CLOB format,

There are two ways to handle the following:

1. Using functions

Type:

Create or ReplaceType str2tblType as Table  of varchar2(4000)

function:

 create  or  replace  function   Tab2clob (P_str2tbltype Str2tbltype,     

inch VARCHAR2 DEFAULT ' , ' RETURN  is  

BEGIN  

 for inch (SELECT    

 from TABLE  

ORDER  by  

L_result:=| | ||  

END LOOP;

RETURN LTrim (L_result, P_delim);

END;

Test:

Initialize data:

BEGIN

 for inch 1 10000 LOOP

INSERT  into VALUES (Sys_guid ());

END LOOP;

END;

SQL statement to test:

SELECT tab2clob (CAST as str2tbltype)) attributes

 from Ts1

WHERE <  +

Attention:

SQL statement error as follows: Due to different types

SELECT sys_util.tab2clob (CAST as str2tbltype))

 from (SELECTDISTINCT from EMP)

Will throw the following exception information:

Because VARCHAR2 is declared in Str2tbltype, but now Deptno is a number, the data type is inconsistent, so you can use To_char to see it for conversion to avoid the above error:

SELECT sys_util.tab2clob (CAST as str2tbltype))

 from (SELECTDISTINCT from EMP)

Two: SQL-supplied statements that handle XML using Oracle: Xmlagg ()

The SQL statements are as follows:

SELECT RTrim || ' , ' ORDER  by ename)

. Getclobval (),

' , ' ) attributes,

Deptno

 from EMP

GROUP  by Deptno;

reprinted from: http://blog.csdn.net/l2tp1012/article/details/30744371

Wm_concat characters more than 4000 of the processing method