Oracle PlSql 问题集锦 - replace 隐式转换的探索

问题描述

在EBS提请求输出xml文件的时候，请求先是报错【ORA-22828 】输入样式或替换参数超过了32k大小限制；修改后有报错【ORA-06502】数字或值错误。

问题分析

这个 错误“【ORA-22828 】输入样式或替换参数超过了32k大小限制” 网上一搜有很多解决方法，这里就不多研究了。但继这个问题之后引发的错误“【ORA-06502】数字或值错误” 确值得写一下。因为replace是支持时间数字等格式变为字符的隐式转换。

问题处理

正常情况下是这样的：

DECLARE
  l_xml_body CLOB;
BEGIN
  l_xml_body := 'output [date]' || chr(13) || 'output [number]';
  l_xml_body := REPLACE(l_xml_body, 'date', to_date(SYSDATE, 'YYYY-MM-DD'));
  l_xml_body := REPLACE(l_xml_body, 'number', 1);
  dbms_output.put_line(l_xml_body);
END;

执行结果：

但是当CLOB进行replace或是拼接后再有时间格式/数字格式的replace替换将报“【ORA-06502】数字或值错误”

DECLARE
  l_xml_body CLOB;
  l_xml_tmp  CLOB;

  FUNCTION replace_with_clob(p_source  IN CLOB,
                             p_search  IN VARCHAR2,
                             p_replace IN CLOB) RETURN CLOB IS
    l_pos PLS_INTEGER;
  BEGIN
    l_pos := instr(p_source, p_search);
    IF l_pos > 0 THEN
      RETURN substr(p_source, 1, l_pos - 1) || p_replace || substr(p_source,
                                                                   l_pos +
                                                                   length(p_search));
    END IF;
    RETURN p_source;
  END replace_with_clob;
BEGIN
  l_xml_body := 'output [date]' || chr(13) || 'output [number]' || chr(13) ||
                'output [clob]';

  l_xml_tmp  := 'S' || rpad('t', 32766, 't') || 'E';
  l_xml_body := replace_with_clob(l_xml_body, 'clob', l_xml_tmp);
  l_xml_body := REPLACE(l_xml_body, 'date', to_date(SYSDATE, 'YYYY-MM-DD'));
  l_xml_body := REPLACE(l_xml_body, 'number', 1);
  dbms_output.put_line(substr(l_xml_body, 1, 200));
END;

执行结果：

解决方案

遇到这种情况时，需人为转换格式，避免隐式转换错误产生。

DECLARE
  l_xml_body CLOB;
  l_xml_tmp  CLOB;

  FUNCTION replace_with_clob(p_source  IN CLOB,
                             p_search  IN VARCHAR2,
                             p_replace IN CLOB) RETURN CLOB IS
    l_pos PLS_INTEGER;
  BEGIN
    l_pos := instr(p_source, p_search);
    IF l_pos > 0 THEN
      RETURN substr(p_source, 1, l_pos - 1) || p_replace || substr(p_source,
                                                                   l_pos +
                                                                   length(p_search));
    END IF;
    RETURN p_source;
  END replace_with_clob;
BEGIN
  l_xml_body := 'output [date]' || chr(13) || 'output [number]' || chr(13) ||
                'output [clob]';

  l_xml_tmp  := 'S' || rpad('t', 32766, 't') || 'E';
  l_xml_body := replace_with_clob(l_xml_body, 'clob', l_xml_tmp);
  l_xml_body := REPLACE(l_xml_body, 'date', to_char(SYSDATE, 'YYYY-MM-DD'));
  l_xml_body := REPLACE(l_xml_body, 'number', '1');
  dbms_output.put_line(substr(l_xml_body, 1, 200));
END;

执行结果：

问题总结

在项目上要求plsql的输出信息往往是会超过oracle的字符最大长度32767（32K），但当使用lob类型操作的时候，是需要注意以下三点的：
1.CLOB字段A去替换CLOB字段B中的某个字符串BBB，当A的大小超过32KB之后，无法使用replace直接替换。
2.当CLOB字段B大小超过32KB之后，replace方式中如果存在隐式转换，会报错。小于32KB不会报错。
3.replace只能处理字符串类型，长度不超过32768