Oracle中如何对all_tab_cols.data_default列应用谓词进行查询？

问题 #

Oracle系统视图all_tab_cols中的data_default列类型为LONG，当列默认值为表达式时，查询该列会返回字符串形式的结果。例如：

CREATE TABLE tab1 (
  col1 NUMBER DEFAULT mysequence.nextval
);

执行以下查询：

SELECT data_default FROM all_tab_cols WHERE table_name = 'TAB1' AND column_name = 'COL1';

会返回字符串 "MYUSER"."MYSEQUENCE"."NEXTVAL"。

但当尝试以data_default作为LIKE谓词的条件时，比如：

SELECT data_default FROM all_tab_cols 
WHERE table_name = 'TAB1' AND data_default LIKE '"MYUSER"%';

会抛出错误 ORA-00932: 数据类型不一致：预期CHAR得到LONG。即使尝试直接转换该列为字符类型，仍会触发相同错误。此外，解析DBMS_METADATA.GET_DDL的输出成本高且易出错，因此需要找到能对默认值直接应用谓词的可行方案。

解决方案 #

1. 子查询转换LONG为CLOB后过滤 #

利用子查询将LONG类型的data_default转换为CLOB，再在外层查询中使用LIKE谓词。这种方法简单直接，且能避免长度限制问题：

SELECT table_name, column_name, data_default_clob
FROM (
    SELECT table_name, column_name, TO_CLOB(data_default) AS data_default_clob
    FROM all_tab_cols
    WHERE table_name = 'TAB1' -- 先缩小范围，提升效率
)
WHERE data_default_clob LIKE '"MYUSER"%';

2. PL/SQL批量处理 #

如果需要批量查询多表或大数据量，使用PL/SQL块可以绕过SQL层面的LONG类型限制，直接在逻辑中判断：

DECLARE
    v_default_val LONG;
BEGIN
    FOR col_rec IN (
        SELECT table_name, column_name, data_default 
        FROM all_tab_cols 
        WHERE owner = 'MYUSER' -- 可根据需要添加过滤条件
    ) LOOP
        v_default_val := col_rec.data_default;
        IF v_default_val LIKE '"MYUSER"%' THEN
            DBMS_OUTPUT.PUT_LINE(
                col_rec.table_name || '.' || col_rec.column_name || ' 的默认值: ' || v_default_val
            );
        END IF;
    END LOOP;
END;
/

3. XML转换（需注意特殊字符） #

通过XMLTYPE将LONG转换为字符串，适合默认值中无特殊XML字符（如<、>、&）的场景：

SELECT table_name, column_name, data_default_str
FROM (
    SELECT table_name, column_name,
           XMLTYPE('<val>' || data_default || '</val>').extract('/val/text()').getstringval() AS data_default_str
    FROM all_tab_cols
    WHERE table_name = 'TAB1'
)
WHERE data_default_str LIKE '"MYUSER"%';

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