Oracle移除CLOB字段HTML标签超4000字符报ORA-01704问题咨询

问题描述 #

自定义HTML_TO_TEXT函数用于将HTML格式的CLOB字段内容转换为纯文本，初始实现代码如下：

FUNCTION HTML_TO_TEXT(html IN CLOB) RETURN CLOB
IS v_return CLOB;
   BEGIN     
select utl_i18n.unescape_reference(regexp_replace(html, '<.+?>', ' ')) INTO v_return from dual;
return (v_return);
    END;

函数调用方式：

SELECT A, B, C, HTML_TO_TEXT(BLobField) FROM t1

当入参字段内容长度不超过4000字符时函数运行正常，内容超过4000字符时抛出如下报错：

ORA-01704: string literal too long
01704. 00000 -  "string literal too long"
*Cause:    The string literal is longer than 4000 characters.
*Action:   Use a string literal of at most 4000 characters.
           Longer values may only be entered using bind variables.

尝试新增CLOB类型中间变量承接处理结果的修改方案未生效，修改后的无效代码如下：

FUNCTION HTML_TO_TEXT(html IN CLOB) RETURN CLOB
 IS v_return CLOB;
 "stringa" CLOB;
   BEGIN 
    SELECT regexp_replace(html, '<.+?>', ' ') INTO "stringa" FROM DUAL;
    select utl_i18n.unescape_reference("stringa") INTO v_return from dual;
    return (v_return);
END;

报错根因 #

• 报错核心原因是代码中使用SELECT ... FROM DUAL的方式做函数运算赋值，该上下文属于SQL执行环境，Oracle会隐式将CLOB类型转换为SQL上下文默认最大长度4000字符的VARCHAR2类型，入参超长时就会触发字符串长度超限错误，和函数内是否定义CLOB中间变量没有关系。

可行解决方案 #

直接使用PL/SQL原生变量赋值语法完成运算，跳过SELECT FROM DUAL的SQL上下文，避免隐式类型转换，修正后的函数代码如下：

FUNCTION HTML_TO_TEXT(html IN CLOB) RETURN CLOB
IS 
  v_return CLOB;
  v_stripped_clob CLOB;
BEGIN
  -- PL/SQL原生上下文直接操作CLOB，无4000字符长度限制
  v_stripped_clob := REGEXP_REPLACE(html, '<.+?>', ' ');
  v_return := UTL_I18N.UNESCAPE_REFERENCE(v_stripped_clob);
  RETURN v_return;
END;

补充优化建议 #

• 若使用Oracle 12c及以上版本，可通过修改库级参数MAX_STRING_SIZE=EXTENDED将SQL上下文VARCHAR2长度上限提升至32767，但该参数修改影响全库所有业务，非必要不推荐使用。
• 处理超过10MB的超大CLOB内容时，REGEXP_REPLACE性能会明显下降，可搭配DBMS_LOB包按片段拆分循环处理，降低内存占用提升执行效率。
• 业务逻辑中处理CLOB类型字段时，尽量避免不必要的SELECT ... FROM DUAL赋值操作，直接在PL/SQL上下文调用内置函数即可规避绝大多数隐式类型转换导致的长度报错。

内容的提问来源于stack exchange，提问作者gt.guybrush