Oracle大CLOB用DISTINCT遇ORA-64203错误求解决

你遇到的ORA-64203错误，核心原因是普通substr+to_char处理CLOB时，字符集转换后实际占用的字节数超过了VARCHAR2的4000字节上限——哪怕你指定了取前4000字符，不同字符集（比如UTF8）下单个字符可能占多字节，转换后总字节数会突破缓冲区限制。下面给你两个可行的解决方案，按需选择：

方案1：用DBMS_LOB.SUBSTR安全截断CLOB并去重 #

Oracle提供了专门针对LOB类型的DBMS_LOB.SUBSTR函数，它直接从CLOB中提取指定字节数的内容，返回VARCHAR2类型，能避免字符集转换导致的缓冲区溢出问题。同时优化你的查询结构（去掉冗余括号、简化无效条件）：

SELECT sub_synonym
FROM (
    -- 用DBMS_LOB.SUBSTR取前4000字节的CLOB内容，再做DISTINCT去重
    SELECT DISTINCT DBMS_LOB.SUBSTR(sub_synonym, 4000, 1) AS sub_synonym
    FROM ingr
    JOIN sub_ca ON ingr.id_d = sub_ca.id_d
    WHERE ingr.log_d = 0
      -- 原条件sub_ca.sub_synonym LIKE '%'等价于非空判断，更高效的写法是：
      AND sub_ca.sub_synonym IS NOT NULL
)
WHERE ROWNUM <= 1500;

为什么这个方案可行？ #

• DBMS_LOB.SUBSTR的第二个参数是字节数，不是字符数，确保返回的内容不会超过VARCHAR2的4000字节上限；
• 直接对CLOB提取子串，避免了to_char转换时的额外字符集处理，从根源上解决ORA-64203错误；
• 嵌套子查询先完成去重，再通过ROWNUM限制结果数量，符合你“先去重再取前1500条”的需求。

方案2：基于哈希值实现完整CLOB去重（无截断） #

如果你需要对完整CLOB内容去重（不想因为截断导致误判重复），可以利用Oracle的DBMS_CRYPTO函数生成CLOB的哈希值，通过哈希值分组去重：

SELECT sub_synonym
FROM (
    SELECT 
        sub_synonym,
        -- 按CLOB的MD5哈希分组，给每组第一条记录标记rn=1
        ROW_NUMBER() OVER (PARTITION BY DBMS_CRYPTO.HASH(sub_synonym, 2) ORDER BY sub_synonym) AS rn
    FROM ingr
    JOIN sub_ca ON ingr.id_d = sub_ca.id_d
    WHERE ingr.log_d = 0
      AND sub_ca.sub_synonym IS NOT NULL
)
WHERE rn = 1  -- 只保留每组的第一条（即去重后的结果）
  AND ROWNUM <= 1500;

注意事项： #

• DBMS_CRYPTO.HASH的第二个参数2代表MD5算法，你也可以用1（SHA-1）等其他哈希算法；
• 使用DBMS_CRYPTO需要DBA授予权限，执行GRANT EXECUTE ON SYS.DBMS_CRYPTO TO 你的用户名;即可；
• 这个方案不会截断CLOB，能保证去重的精确性，但性能会比方案1稍差（需要计算每个CLOB的哈希）。

关于GROUP BY的补充 #

你提到GROUP BY不适用于该字段，其实是因为之前用to_char(substr(...))的转换方式有问题。如果用方案1中的DBMS_LOB.SUBSTR，GROUP BY是完全可行的，比如：

SELECT DBMS_LOB.SUBSTR(sub_synonym, 4000, 1) AS sub_synonym
FROM ingr
JOIN sub_ca ON ingr.id_d = sub_ca.id_d
WHERE ingr.log_d = 0
  AND sub_ca.sub_synonym IS NOT NULL
GROUP BY DBMS_LOB.SUBSTR(sub_synonym, 4000, 1)
FETCH FIRST 1500 ROWS ONLY;  -- Oracle 12c+支持的更简洁的分页语法

内容的提问来源于stack exchange，提问作者Sir. Hedgehog