Listagg string concatenation results in too long problem solving

Because the business needs to count some data, it needs to be spliced into the Java code to perform a concatenation of the different departments ' user IDs that are involved in the table.

Start preparing to use Wm_concat, and then find out that the hint does not have this function. Checked the original is because this function relies on Wmsys users, this version is not configured, but only to retreat and then choose the Listagg function.

Round down the effect is good, the desired effect can be achieved.

Select
	r.department_id law_dept_id,
	(
		select
			Listagg (
				rt.extracted_person_ids,
				', '
			, ' WITHIN GROUP (
 					ORDER by Rt.extracted_person_ids
			)
			from Random_task RT,
			sys_department SD
		WHERE
			Rt. creat_department_id = sd. department_id and
		SD. Department_number like CONCAT (r.department_num, '% ') and
		rt.distributed_state = 1 and
		Rt. state = 1 and
		Rt. Launch_date BETWEEN to_date (' 2017-07-05 ', ' yyyy-mm-dd ') and
		(To_date (' 2017-07-05 ', ' yyyy-mm-dd ') + 1)
	) law_ User_str
	from Random_user_count R
	ORDER by R.area_code,
	R.department_num;

But one day, this section of SQL in the run when the error suddenly, prompted the "string connection results too long", online query before you know, the original use of Listagg stitching, the return of the result is varchar type, and has the maximum length limit, you can turn into club or intercept ...

Later, according to a variety of links to find a relatively reliable answer, click on the Open link

The SQL after the change is as follows:

Select
	r.department_id law_dept_id,
	(
		select
- 			listagg (
-- 				Rt.extracted_person_ids ,
-- 				', '
-- 			WITHIN GROUP (--ORDER by 				--  					rt.extracted_person_ids
-- 			)
			Xmlagg (xmlparse (Content rt.extracted_person_ids| | ', ' wellformed) Order by Rt.extracted_person_ids). Getclobval ()
		from
			Random_task RT,
			sys_department SD
		WHERE
			Rt. creat_department_id = sd. department_id and
		SD. Department_number like CONCAT (r.department_num, '% ') and
		rt.distributed_state = 1 and
		Rt. state = 1 and
		Rt. Launch_date BETWEEN to_date (' 2017-07-05 ', ' yyyy-mm-dd ') and
		(To_date (' 2017-07-05 ', ' yyyy-mm-dd ') + 1)
	) law_ User_str
	from Random_user_count R
	ORDER by R.area_code,
	R.department_num;

The

resolves the problem and the result is normal.