The below was used listagg.. I changed it to XMLAGG yesterday.  It is was worked. Now It is giving Error.. ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 5910, maximum: 4000)

CREATE OR REPLACE FORCE VIEW "CPZOI39"."V_EQUIPMENT_GROUP_MEMBER_COUNT" ("VERIFIER_GROUP_ID", "MEMBERS", "MEMBER_EQUIPMENT_NAME") AS

SELECT VGM.verifier_group_id,

count(VGM.verifier_id) members,

substr(

XMLCast(

XMLAGG( XMLELEMENT(e, ',' || v.verifier_name) )

as CLOB

)

, 2

) AS member_equipment_name

FROM VERIFIER V,VERIFIER_GROUP_MEMBER VGM

WHERE V.verifier_id = VGM.verifier_id

GROUP BY vgm.verifier_group_id;

SELECT count(profile_id) FROM (SELECT T.*, row_number() over (order by lower(profile_name) ASC ) rownumber

FROM (

SELECT

distinct ppg.profile_id as pid,

pd.profile_id as profile_id,

pd.profile_name as profile_name,

pd.last_modified as last_modified,

pd.type_code as type_code,

vuo.print_name as created_by,

vuo.id as created_by_id,

vuo.deleted as deleted,

(case

when pd.type_code = 'sd'

then

case

when pd.auto_install = 1 then 1

else 0

end

else 2

end) as auto_installable,

pd.auto_install as auto_install,

(case

when pd.type_code = 'sd'

then

case

when pd.auto_install = 1 then TO_CHAR(pd.auto_install_date)

else '--'

end

else 'NA'

end) as auto_install_date,

v_modify.id as user_id,

v_modify.print_name as modified_by,

v_modify.deleted as modify_deleted,

V_PROFILE_STATUS.noncompliant noncompliant,

(case

when V_PROFILE_STATUS.noncompliant is not null

then

case

when V_PROFILE_APP_FILE_COUNT.cnt > 0 then '2'

else '1'

end

else case

when V_PROFILE_APP_FILE_COUNT.cnt > 0 then '1'

else '--'

end

end) as profile_status,

V_PROFILE_APP_UPGRADE.upgrade upgrade,

vegb.bu_name as business_unit,

vegb.bu_id as bu_id,

listagg(vg.verifier_group_name,',') within group (order by ppg.verifier_group_id, vg.verifier_group_id )  as member_group,

listagg(ppg.verifier_group_id,',') within group (order by ppg.verifier_group_id )  as member_group_ids,

listagg(vegmc.member_equipment_name,',') within group ( order by vegmc.member_equipment_name) AS member_equipment_name,

vpfsl.assigned_files,

vpfsl.assigned_platform_applications,

decode(pd.winxp_path,null,'',pd.winxp_path) || decode(pd.winxpe_path,null,'', ','||pd.winxpe_path) || decode(pd.wince_path,null,'', ','||pd.wince_path) as destination_folder

FROM

profile_data pd,

V_PROFILE_APP_UPGRADE,

V_PROFILE_STATUS,

V_PROFILE_APP_FILE_COUNT,

V_USER_OPERATOR vuo,

V_USER_OPERATOR v_modify,

PROFILE_PLATFORM_GROUPS ppg,

V_EQUIPMENT_GROUP_BU vegb,

v_equipment_group_member_count vegmc,

verifier_group vg,

v_profile_file_software_list vpfsl

WHERE

V_PROFILE_APP_UPGRADE.profile_id = pd.profile_id

and V_PROFILE_STATUS.profile_id = pd.profile_id

and V_PROFILE_APP_FILE_COUNT.profile_id = pd.profile_id

and pd.CREATED_BY = vuo.ID(+)

and vuo.user_type not like '%operator%'

and pd.USER_ID = v_modify.ID(+)

and v_modify.user_type not like '%operator%'

and pd.profile_id = ppg.profile_id

and ppg.verifier_group_id = vegb.verifier_group_id

and ppg.verifier_group_id = vg.verifier_group_id

and ppg.verifier_group_id = vegmc.verifier_group_id

and pd.profile_id = vpfsl.profile_id

and  vegb.bu_id IN ( 1000, 1025, 1026, 1075, 1100, 0)

group by

ppg.profile_id,

pd.profile_id ,

pd.profile_name ,

pd.last_modified,

pd.type_code ,

vuo.print_name,

vuo.id ,

vuo.deleted ,

pd.auto_install,

v_modify.id,

v_modify.print_name,

v_modify.deleted ,

V_PROFILE_STATUS.noncompliant ,

pd.auto_install_date,

V_PROFILE_APP_UPGRADE.upgrade ,

vegb.bu_name ,

vegb.bu_id,

V_PROFILE_APP_FILE_COUNT.cnt,

vpfsl.assigned_files,

vpfsl.assigned_platform_applications,

pd.winxp_path,

pd.winxpe_path,

pd.wince_path

) T);

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 5910, maximum: 4000)

22835. 00000 -  "Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: %s, maximum: %s)"

*Cause:    An attempt was made to convert CLOB to CHAR or BLOB to RAW, where

the LOB size was bigger than the buffer limit for CHAR and RAW

types.

Note that widths are reported in characters if character length

semantics are in effect for the column, otherwise widths are

reported in bytes.

*Action:   Do one of the following

1. Make the LOB smaller before performing the conversion,

for example, by using SUBSTR on CLOB

2. Use DBMS_LOB.SUBSTR to convert CLOB to CHAR or BLOB to RAW.