ORA-22828 输入样式或替换参数超过了32k大小限制

今天调试程序报以下错误：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 := "<resp>#result#</resp>";
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 := "<resp>#result#</resp>";
ret_cb := replace_with_clob（ret_cb, "#result#", cb）;
dbms_output.put_line（substr（ret_cb, 1, 100））;
end;
/执行成功！-----------------------------------Dylan Presents.更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址