使用LISTAGG报Ora-1489拼接字符串过长错误，尝试用XMLAGG解决

错误原因 #

ORA-01489报错是因为LISTAGG函数拼接后的字符串长度超出了VARCHAR2类型的上限（默认最大4000字节，开启32k扩展后最大32767字节），如果拼接结果确实会超出上限，使用支持返回CLOB类型的XMLAGG函数是最常用的规避方案。

现有代码问题 #

你已经注释了XMLAGG的写法，但存在两处错误：

• 多余嵌套了一层XMLAGG，不需要双重封装
• 没有处理拼接后末尾多余的逗号，也没有规避XML特殊字符转义的问题

修正后的代码 #

只需要把子查询中LISTAGG的部分替换为以下逻辑即可：

-- 注释原有LISTAGG逻辑
-- listagg((PEA.EMAIL_ADDRESS || ','|| LOBS.EMAIL1), ',') within group (order by LOCT.internal_location_code) LEARN_OBS_EMAIL
-- 替换为修正后的XMLAGG逻辑
RTRIM(
  XMLAGG(
    XMLELEMENT(E, PEA.EMAIL_ADDRESS || ',' || LOBS.EMAIL1 || ',')
    .EXTRACT('//text()') 
    ORDER BY LOCT.internal_location_code
  ).GETCLOBVAL(),
  ','
) AS LEARN_OBS_EMAIL

完整修正后的SQL如下：

SELECT DISTINCT 
  LEARN_OBS_LCODE                       "KEY", 
  'Results'                              "TEMPLATE", 
  'en-US'                                "LOCALE", 
  'html'                                 OUTPUT_FORMAT, 
  'TTX Checklist Activity Notification' "OUTPUT_NAME", 
  'EMAIL'                                DEL_CHANNEL, 
  LEARN_OBS_EMAIL                        "PARAMETER1",
  -- "PARAMETER2",
  'ejjc.fa.sender@workflow.mail.us6.oraclecloud.com' "PARAMETER3", 
  '(' || LEARN_OBS_LNAME || ') ' || 'Observation Checklist Pending' 
  || ' - ' 
  || TO_CHAR(sysdate, 'MM/DD/YYYY')      "PARAMETER4", 
  'true'                                 "PARAMETER6"
FROM  (
  SELECT DISTINCT 
    RTRIM(LOCT.internal_location_code) LEARN_OBS_LCODE,
    RTRIM(LOC.location_name) LEARN_OBS_LNAME,
    RTRIM(
      XMLAGG(
        XMLELEMENT(E, PEA.EMAIL_ADDRESS || ',' || LOBS.EMAIL1 || ',')
        .EXTRACT('//text()') 
        ORDER BY LOCT.internal_location_code
      ).GETCLOBVAL(),
      ','
    ) AS LEARN_OBS_EMAIL
  FROM fusion.per_all_people_f PAPF 
  INNER JOIN fusion.per_person_names_f PER 
    ON PAPF.person_id = PER.person_id 
    AND TRUNC(sysdate) BETWEEN PER.effective_start_date AND PER.effective_end_date 
    AND PER.name_type = 'GLOBAL'
  LEFT JOIN FUSION.PER_USERS PLU  
    ON PLU.PERSON_ID = PAPF.PERSON_ID  
    AND TRUNC(sysdate) BETWEEN PLU.START_DATE AND NVL(PLU.END_DATE,TRUNC(sysdate))
    AND PLU.ACTIVE_FLAG = 'Y'
    --AND PLU.USERNAME  = FND_GLOBAL.USER_NAME 
  LEFT JOIN FUSION.PER_USER_ROLES PUR     
    ON PUR.USER_ID = PLU.USER_ID 
    AND TRUNC(sysdate) BETWEEN PUR.START_DATE AND NVL(PUR.END_DATE, TRUNC(sysdate))
  LEFT JOIN FUSION.PER_ROLES_DN PR_BASE   
    ON PR_BASE.ROLE_ID = PUR.ROLE_ID
  LEFT JOIN FUSION.PER_ROLES_DN_TL PLR    
    ON PLR.ROLE_ID = PR_BASE.ROLE_ID 
    AND PLR.LANGUAGE = USERENV('LANG')  
    AND PLR.ROLE_NAME IS NOT NULL       
  INNER JOIN fusion.per_all_assignments_f ASG 
    ON ASG.person_id = PAPF.person_id 
    AND TRUNC(sysdate) BETWEEN ASG.effective_start_date AND ASG.effective_end_date 
    AND ASG.primary_flag = 'Y' 
    AND ASG.assignment_type IN ( 'E', 'C','N' ) 
    AND ASG.effective_latest_change = 'Y' 
    AND ASG.assignment_status_type <> 'INACTIVE' 
  LEFT JOIN per_location_details_f_vl LOC 
    ON ASG.location_id = LOC.location_id 
    AND TRUNC(sysdate) BETWEEN LOC.effective_start_date AND LOC.effective_end_date 
  LEFT JOIN per_locations LOCT 
    ON LOC.location_id = LOCT.location_id 
  LEFT JOIN fusion.per_email_addresses PEA 
    ON PEA.person_id = PAPF.person_id 
    AND TRUNC(sysdate) BETWEEN PEA.date_from AND NVL(PEA.date_to, TRUNC(sysdate)) 
    AND PEA.email_type = 'W1' 
  INNER JOIN LEARN_OBSERVER LOBS
    ON ASG.LOCATION_ID = LOBS.LOCATION_ID
  WHERE TRUNC(sysdate) BETWEEN PAPF.effective_start_date AND PAPF.effective_end_date
    --and papf.person_number = '38148'                                  
    AND UPPER(PR_BASE.ROLE_COMMON_NAME) = 'TTX_LEARNING_OBSERVER_BY_LOCATION_DATA'
    --AND UPPER(PLR.ROLE_NAME) = 'TTX LEARNING OBSERVER BY LOCATION'
  GROUP BY LOCT.internal_location_code , LOC.location_name 
)

注意事项 #

• 修正后返回的LEARN_OBS_EMAIL为CLOB类型，若后续使用场景要求VARCHAR2类型，且确认拼接后长度不超过32767字节，可在外层嵌套TO_CHAR()转换
• 如果你使用的是Oracle 12cR2及以上版本，也可以选择在LISTAGG后添加ON OVERFLOW TRUNCATE '...' WITH COUNT参数处理超长问题，该方案会截断超出长度的内容，适合可接受截断的场景

内容的提问来源于stack exchange，提问作者Suresh