LISTAGG导致连接过长

ORacle使用LISTAGG拼接字符串，查询出的结果报错：

ORA-01489: 字符串连接的结果过长

这是原语句:

SELECT i."ID"
    ,i."CORP_NAME"
    ,i."AREA_CODE"
    ,i."AREA_NAME"
    ,i."SOCIAL_CREDIT_CODE"    
    ,l.type
    ,l.LICENSE_NUMBER
FROM YYJG_CORP_INFO i
LEFT JOIN (
    SELECT SOCIAL_CREDIT_CODE
        ,LISTAGG(type, ',') WITHIN
    GROUP (
            ORDER BY SOCIAL_CREDIT_CODE
            ) AS type
        ,LISTAGG(LICENSE_NUMBER, ',') WITHIN
    GROUP (
            ORDER BY SOCIAL_CREDIT_CODE
            ) AS LICENSE_NUMBER
    FROM YYJG_CORP_LICENCE
    GROUP BY SOCIAL_CREDIT_CODE
    ) l ON l.SOCIAL_CREDIT_CODE = i.SOCIAL_CREDIT_CODE

修改表的字符串的大小，换成varchar2为4000都不行，使用:

xmlagg和xmlparse

SELECT SOCIAL_CREDIT_CODE
,xmlagg(xmlparse(content type||',')order by SOCIAL_CREDIT_CODE).getclobval() as type
,xmlagg(xmlparse(content LICENSE_NUMBER||',')order by SOCIAL_CREDIT_CODE).getclobval() as LICENSE_NUMBER
from YYJG_CORP_LICENCE GROUP BY SOCIAL_CREDIT_CODE

搞定