Oracle带DISTINCT查询报ORA-00997 LONG类型非法使用错误

LONG是Oracle已废弃的遗留数据类型，存在严格的使用限制：不支持DISTINCT、GROUP BY、UNION、等值匹配判断等操作。ORA-00997报错是因为DISTINCT关键字需要对A.COLUMN_EXPRESSION字段做去重比对，而该字段为LONG类型不支持该操作；移除DISTINCT后不需要对该字段做去重校验，因此可以正常执行。

核心思路是先将LONG类型的COLUMN_EXPRESSION转换为VARCHAR2类型，再执行DISTINCT去重，以下是三种可直接落地的方案，根据数据库版本和权限选择即可。

方案1：创建通用转换函数（全版本兼容，一次创建永久可用） #

该方案利用PL/SQL支持隐式将LONG类型赋值给VARCHAR2变量的特性实现转换，需要用户拥有CREATE PROCEDURE权限。
首先执行函数创建语句：

CREATE OR REPLACE FUNCTION LONG_TO_VC(P_LONG IN LONG) RETURN VARCHAR2 IS
BEGIN
  -- 12c之前版本SQL层面VARCHAR2最大支持4000字节，12c开启MAX_STRING_SIZE=EXTENDED后可改为32767
  RETURN SUBSTR(P_LONG, 1, 4000);
END;
/

将原SQL内层子查询中的EXP.COLUMN_EXPRESSION替换为LONG_TO_VC(EXP.COLUMN_EXPRESSION) AS COLUMN_EXPRESSION即可正常执行带DISTINCT的查询。

方案2：XML转换（无额外权限要求，全版本兼容） #

如果没有创建函数的权限，可以利用Oracle XML解析时自动将LONG类型转换为文本节点的特性实现转换，不需要创建任何对象，直接将原SQL内层的EXP.COLUMN_EXPRESSION替换为以下表达式：

DBMS_XMLGEN.CONVERT(
  EXTRACTVALUE(
    XMLTYPE('<root><col>' || EXP.COLUMN_EXPRESSION || '</col></root>'),
    '/root/col/text()'
  )
) AS COLUMN_EXPRESSION

其中DBMS_XMLGEN.CONVERT用于处理表达式中可能存在的XML特殊字符，避免解析报错。

方案3：12cR2及以上版本简便写法 #

如果数据库版本是12cR2或更高，可以直接用内置类型转换函数实现，写法最简洁，直接替换原字段即可：

TO_VARCHAR(TO_CLOB(EXP.COLUMN_EXPRESSION)) AS COLUMN_EXPRESSION

• 函数索引的表达式长度受索引创建规则限制，绝大多数场景下不会超过4000字节，转换时截断到4000字节不会丢失有效内容。
• 禁止直接对LONG类型字段使用TO_CHAR函数，会触发参数类型不匹配报错。

SELECT DISTINCT
  B.OWNER TABLE_OWNER,
  B.TABLE_NAME,
  A.INDEX_OWNER,
  A.INDEX_NAME,
  A.COLUMN_EXPRESSION,
  NVL(CNT,0) RCNT
FROM
  (
    SELECT
      COL.INDEX_OWNER,
      IND.INDEX_NAME,
      IND.TABLE_OWNER,
      IND.TABLE_NAME,
      TO_VARCHAR(TO_CLOB(EXP.COLUMN_EXPRESSION)) COLUMN_EXPRESSION,
      1 CNT
    FROM
      ALL_INDEXES IND,
      ALL_IND_COLUMNS COL,
      ALL_IND_EXPRESSIONS EXP
    WHERE
      IND.TABLE_NAME   = COL.TABLE_NAME
    AND IND.INDEX_NAME = COL.INDEX_NAME
    AND IND.INDEX_NAME = EXP.INDEX_NAME
    AND IND.INDEX_TYPE LIKE 'FUN%'
  )
  A,
  ALL_INDEXES B
WHERE
  A.TABLE_NAME (+)   = B.TABLE_NAME
AND A.TABLE_OWNER (+)= B.TABLE_OWNER
AND B.TABLE_NAME    IN ('GA_EXPENDITURE_COMMITMENT_F','GA_ABC_DETAIL_RPTG_F')
AND B.TABLE_OWNER    = 'EDWFIN';

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