Oracle中如何比较LONG类型与CLOB类型数据

错误原因 #

user_views的text字段为LONG类型，Oracle对该旧类型有诸多使用限制：无法在JOIN查询的SELECT列表中直接用to_lob()转换，也不能直接作为dbms_lob.compare()的参数，这直接导致了类型不一致的报错。

解决方案 #

通过子查询预先将LONG类型转换为CLOB，再与备份表关联比较，即可避开LONG类型的限制。以下是两种可行写法：

方法一：子查询预转换LONG为CLOB（优先推荐） #

SELECT 
    CASE 
        WHEN dbms_lob.compare(uv_clob.text, avb.text) = 0 THEN 0
        ELSE 1
    END AS text_compare_result
FROM all_views_backup avb
JOIN (
    SELECT view_name, to_lob(text) AS text
    FROM user_views
) uv_clob ON avb.view_name = uv_clob.view_name;

方法二：通过XMLTYPE间接转换LONG为CLOB #

如果子查询方式存在特殊限制，可借助XML类型完成转换：

SELECT 
    CASE 
        WHEN dbms_lob.compare(
            xmltype('<x>' || uv.text || '</x>').extract('//text()').getclobval(),
            avb.text
        ) = 0 THEN 0
        ELSE 1
    END AS text_compare_result
FROM all_views_backup avb
JOIN user_views uv ON avb.view_name = uv.view_name;

说明 #

• dbms_lob.compare()默认返回0表示两个CLOB内容完全相同，返回非0值表示内容不同；通过CASE语句可将结果统一转换为你需要的0（相同）和1（不同）。
• 方法一的子查询方式更高效直观，是优先选择的方案。

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