今天调试程序报以下错误：
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.
可用以下脚本重现：
declare
cb clob;
ret_cb CLOB;
begin
cb := rpad(‘x’, 32767, ‘x’)||’x’;
ret_cb := ‘#result#’;
ret_cb := replace(ret_cb, ‘#result#’, cb);
end;
/
解决方式如下：
编写函数：
create or replace 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
cb clob;
ret_cb CLOB;
begin
cb := rpad(‘x’, 32767, ‘x’)||’x’;
ret_cb := ‘#result#’;
ret_cb := replace_with_clob(ret_cb, ‘#result#’, cb);
dbms_output.put_line(substr(ret_cb, 1, 100));
end;
/
执行成功！
———————————–
Dylan Presents.
