Oracle 11g肯定提高了CLOB的可用性,使大多数字符串函数重载,因此它们现在可以与CLOB本机一起工作.
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.
仅当要替换的第三个参数是具有超过32k字符的CLOB时才会发生这种情况.
(Oracle Database 11g企业版11.2.0.3.0版 – 64位生产)
测试用例：
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,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
这是一个功能粗略的初稿,它将在某些限制下完成工作,但尚未经过很好的测试：
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;
它只对搜索词的第一个实例进行单个替换.
declare
v2 varchar2(32767);
cl_small clob;
cl_big clob;
cl_big2 clob;
begin
v2 := rpad('x',cl_small);
dbms_output.put_line('cl_big[2]:' || length(cl_big2));
cl_big2 := replace_with_clob(cl_big,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
