如何使用CLOB调用REPLACE(不超过32K) [英] How to call REPLACE with CLOB (without exceeding 32K)

问题描述

Oracle 11g确实提高了CLOB的可用性，因为它们重载了大多数字符串函数，因此它们现在可以与CLOB一起使用.

Oracle 11g has certainly improved usability of CLOBs, having overloaded most of the string functions so they now work natively with CLOBs.

但是，一位同事从他的代码中得到了这个错误:

However, a colleague was getting this error from his code:

ORA-22828: input pattern or replacement parameters exceed 32K size limit
22828. 00000 -  "input pattern or replacement parameters exceed 32K size limit"
*Cause:    Value provided for the pattern or replacement string in the form of
           VARCHAR2 or CLOB for LOB SQL functions exceeded the 32K size limit.
*Action:   Use a shorter pattern or process a long pattern string in multiple
           passes.

仅当replace的第三个参数是具有超过32k个字符的CLOB时才会发生.

This only occurred when the third parameter to replace was a CLOB with more than 32k characters.

(Oracle数据库11g企业版11.2.0.3.0版-64位生产)

(Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)

测试用例:

declare
  v2 varchar2(32767);
  cl_small clob;
  cl_big clob;
  cl_big2 clob;
begin
  v2 := rpad('x', 32767, 'x');
  dbms_output.put_line('v2:' || length(v2));
  cl_small := v2;
  dbms_output.put_line('cl_small:' || length(cl_small));
  cl_big := v2 || 'y' || v2;
  dbms_output.put_line('cl_big[1]:' || length(cl_big));
  cl_big2 := replace(cl_big, 'y', cl_small);
  dbms_output.put_line('cl_big[2]:' || length(cl_big2));
  cl_big2 := replace(cl_big, 'y', cl_big); 
  dbms_output.put_line('cl_big[3]:' || length(cl_big2));
end;
/

结果:

v2:32767
cl_small:32767
cl_big[1]:65535
cl_big[2]:98301
ORA-22828: input pattern or replacement parameters exceed 32K size limit

这似乎与文档暗示不一致，该文档暗示替换字符串可能是CLOB-我本以为应该暗示任何CLOB都将被允许，而不仅仅是那些恰好是< ; 32K: http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions153.htm#SQLRF00697

This seems at odds with the docs which imply that the replacement string may be a CLOB - I would have thought this should imply that any CLOB would be allowed, not just those that happen to be <32K: http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions153.htm#SQLRF00697

推荐答案

下面是该函数的初稿，该函数可以在一定的限制下完成工作，但尚未经过很好的测试:

Here is a rough first draft for a function that will do the job with certain limitations, it hasn't been very well tested yet:

function replace_with_clob
  (i_source in clob
  ,i_search in varchar2
  ,i_replace in clob
  ) return clob is
  l_pos pls_integer;
begin
  l_pos := instr(i_source, i_search);
  if l_pos > 0 then
    return substr(i_source, 1, l_pos-1)
        || i_replace
        || substr(i_source, l_pos+length(i_search));
  end if;
  return i_source;
end replace_with_clob;

仅对搜索字词的第一个实例进行一次替换.

It only does a single replace on the first instance of the search term.

declare
  v2 varchar2(32767);
  cl_small clob;
  cl_big clob;
  cl_big2 clob;
begin
  v2 := rpad('x', 32767, 'x');
  dbms_output.put_line('v2:' || length(v2));
  cl_small := v2;
  dbms_output.put_line('cl_small:' || length(cl_small));
  cl_big := v2 || 'y' || v2;
  dbms_output.put_line('cl_big[1]:' || length(cl_big));
  cl_big2 := replace(cl_big, 'y', cl_small);
  dbms_output.put_line('cl_big[2]:' || length(cl_big2));
  cl_big2 := replace_with_clob(cl_big, 'y', cl_big); 
  dbms_output.put_line('cl_big[3]:' || length(cl_big2));
end;
/

v2:32767
cl_small:32767
cl_big[1]:65535
cl_big[2]:98301
cl_big[3]:131069

这篇关于如何使用CLOB调用REPLACE(不超过32K)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！