Oracle LONG类型列拆分多行存储的数据合并为单行问题求助

报错原因 #

Oracle的LONG数据类型限制极多，无法直接用于XMLAGG等聚合函数的入参，也不支持隐式转换为CHAR/CLOB类型，因此触发类型不匹配错误。

方案1：自定义转换函数（支持所有Oracle版本） #

首先创建LONG转CLOB的自定义函数，PL/SQL环境支持LONG到CLOB的直接赋值：

CREATE OR REPLACE FUNCTION fn_long_to_clob(p_id VARCHAR2, p_series NUMBER) 
RETURN CLOB
IS
  v_long_val LONG;
BEGIN
  -- 按行查询对应LONG字段值
  SELECT SERIALIZEDMESSAGE INTO v_long_val
  FROM LOGMESSAGE
  WHERE ID = p_id AND SERIES = p_series;
  -- PL/SQL支持LONG直接赋值给CLOB
  RETURN v_long_val;
END;
/

然后用转换后的CLOB做聚合拼接：

SELECT 
  M.Id,
  -- 拼接同组内容，按拆分序号排序（如果有专门的拆分顺序字段请替换ORDER BY后的字段）
  RTRIM(XMLAGG(XMLELEMENT(E, fn_long_to_clob(M.Id, M.Series), ',').EXTRACT('//text()') ORDER BY M.Series).GetClobVal(),',') AS SERIALIZED_FULL,
  M.Series
FROM LOGENTRY E 
INNER JOIN LOGMESSAGE M ON E.Id = M.Id 
GROUP BY M.Id, M.series;

方案2：内嵌函数（Oracle 12c及以上版本可用，无需单独创建函数） #

如果没有创建函数的权限，可以直接在查询语句中嵌入转换逻辑：

WITH 
FUNCTION fn_long_to_clob(p_id VARCHAR2, p_series NUMBER) 
RETURN CLOB
IS
  v_long_val LONG;
BEGIN
  SELECT SERIALIZEDMESSAGE INTO v_long_val
  FROM LOGMESSAGE
  WHERE ID = p_id AND SERIES = p_series;
  RETURN v_long_val;
END;
SELECT 
  M.Id,
  RTRIM(XMLAGG(XMLELEMENT(E, fn_long_to_clob(M.Id, M.Series), ',').EXTRACT('//text()') ORDER BY M.Series).GetClobVal(),',') AS SERIALIZED_FULL,
  M.Series
FROM LOGENTRY E 
INNER JOIN LOGMESSAGE M ON E.Id = M.Id 
GROUP BY M.Id, M.series;

注意事项 #

1. 如果拼接结果中出现</>等XML转义字符，可以用UTL_I18N.UNESCAPE_REFERENCE函数还原原内容：

UTL_I18N.UNESCAPE_REFERENCE(XMLAGG(...).GetClobVal())

2. 长期最优方案：LONG是Oracle已废弃的字段类型，建议直接将表字段改为CLOB类型，后续无需转换即可直接使用所有聚合函数：

ALTER TABLE LOGMESSAGE MODIFY SERIALIZEDMESSAGE CLOB;

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