Oracle使用UNION操作CLOB列报ORA-00932类型不一致错误如何解决

问题原因 #

Oracle的UNION操作会自动对结果集做去重处理，去重需要对比字段值、执行排序操作，但Oracle原生不支持直接对CLOB类型的字段做等值比较、排序这类操作，所以才会抛出ORA-00932数据类型不一致的报错。即便用同一张表做测试也会触发报错，本质就是UNION的去重逻辑触发了CLOB的使用限制。

解决方案 #

方案1：不需要去重时，替换为UNION ALL #

如果业务场景不需要对合并后的结果去重，直接把UNION改成UNION ALL即可。UNION ALL不会执行去重和排序逻辑，不会触发CLOB的类型限制，是性能最优的解法。
修改后的语句示例：

SELECT nm_wkt FROM UR_C99.CD_VET
UNION ALL
SELECT nm_wkt FROM UR_C99.CD_VET

方案2：需要去重时，根据CLOB长度选择处理方式 #

情况A：CLOB内容长度不超过VARCHAR2上限

如果nm_wkt字段存储的内容长度都没超过VARCHAR2的长度上限（Oracle 11g及以前上限是4000字节，12c及以后开启扩展参数后上限是32767字节），可以将CLOB转成VARCHAR2类型后再执行UNION：

SELECT TO_CHAR(nm_wkt) AS nm_wkt FROM UR_C99.CD_VET
UNION
SELECT TO_CHAR(nm_wkt) AS nm_wkt FROM UR_C99.CD_VET

如果需要返回CLOB类型的结果，可以在外层再做一次类型转换：

SELECT TO_CLOB(nm_wkt) AS nm_wkt FROM (
    SELECT TO_CHAR(nm_wkt) AS nm_wkt FROM UR_C99.CD_VET
    UNION
    SELECT TO_CHAR(nm_wkt) AS nm_wkt FROM UR_C99.CD_VET
) t

情况B：CLOB内容长度超过VARCHAR2上限

如果CLOB存储的内容很长，超过VARCHAR2的上限，可以先对CLOB计算哈希值，通过哈希值去重后再返回原CLOB内容：

WITH all_result AS (
    SELECT nm_wkt, DBMS_CRYPTO.HASH(nm_wkt, 2) AS hash_val FROM UR_C99.CD_VET
    UNION ALL
    SELECT nm_wkt, DBMS_CRYPTO.HASH(nm_wkt, 2) AS hash_val FROM UR_C99.CD_VET
)
SELECT nm_wkt FROM (
    SELECT nm_wkt, ROW_NUMBER() OVER (PARTITION BY hash_val ORDER BY 1) rn FROM all_result
) t WHERE rn = 1

注：该方法依赖DBMS_CRYPTO包的使用权限，哈希对比存在极小概率的碰撞风险，业务校验要求极高的场景可以额外结合DBMS_LOB.COMPARE函数做二次等值校验。

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