oracle 如何用CLOB调用REPLACE（不超过32K）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23126455/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to call REPLACE with CLOB (without exceeding 32K)
提问by Jeffrey Kemp
Oracle 11g has certainly improved usability of CLOBs, having overloaded most of the string functions so they now work natively with CLOBs.
Oracle 11g 确实改进了 CLOB 的可用性，重载了大多数字符串函数，因此它们现在可以与 CLOB 本地工作。
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.
This only occurred when the third parameter to replacewas a CLOB with more than 32k characters.
仅当第三个参数 toreplace是超过 32k 个字符的 CLOB时才会发生这种情况。
(Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
（Oracle 数据库 11g 企业版 11.2.0.3.0 版 - 64 位生产）
Test case:
测试用例：
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;
/
Results:
结果：
v2:32767
cl_small:32767
cl_big[1]:65535
cl_big[2]:98301
ORA-22828: input pattern or replacement parameters exceed 32K size limit
This seems at odds with the docs which imply that the replacement string may be a CLOB - I would have thought this shouldimply 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
这似乎与暗示替换字符串可能是 CLOB 的文档不一致 - 我原以为这应该意味着允许任何 CLOB，而不仅仅是那些恰好小于 32K 的：http: //docs.oracle。 com/cd/E11882_01/server.112/e41084/functions153.htm#SQLRF00697
采纳答案by Jeffrey Kemp
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
回答by MT0
You can create a function to handle CLOBvalues of any length:
您可以创建一个函数来处理CLOB任意长度的值：
CREATE FUNCTION lob_replace(
i_lob IN clob,
i_what IN varchar2,
i_with IN clob,
i_offset IN INTEGER DEFAULT 1,
i_nth IN INTEGER DEFAULT 1
) RETURN CLOB
AS
o_lob CLOB;
n PLS_INTEGER;
l_lob PLS_INTEGER;
l_what PLS_INTEGER;
l_with PLS_INTEGER;
BEGIN
IF i_lob IS NULL
OR i_what IS NULL
OR i_offset < 1
OR i_offset > DBMS_LOB.LOBMAXSIZE
OR i_nth < 1
OR i_nth > DBMS_LOB.LOBMAXSIZE
THEN
RETURN NULL;
END IF;
n := NVL( DBMS_LOB.INSTR( i_lob, i_what, i_offset, i_nth ), 0 );
l_lob := DBMS_LOB.GETLENGTH( i_lob );
l_what := LENGTH( i_what );
l_with := NVL( DBMS_LOB.GETLENGTH( i_with ), 0 );
DBMS_LOB.CREATETEMPORARY( o_lob, FALSE );
IF n > 0 THEN
IF n > 1 THEN
DBMS_LOB.COPY( o_lob, i_lob, n-1, 1, 1 );
END IF;
IF l_with > 0 THEN
DBMS_LOB.APPEND( o_lob, i_with );
END IF;
IF n + l_what <= l_lob THEN
DBMS_LOB.COPY( o_lob, i_lob, l_lob - n - l_what + 1, n + l_with, n + l_what );
END IF;
ELSE
DBMS_LOB.APPEND( o_lob, i_lob );
END IF;
RETURN o_lob;
END;
/
Oracle 11g R2 Schema Setup:
Oracle 11g R2 架构设置：
CREATE TABLE table_name ( value clob)
/
CREATE TABLE replacements ( str VARCHAR2(4000), repl CLOB )
/
DECLARE
str VARCHAR2(4000) := 'value';
r CLOB;
c1l CLOB;
c1m CLOB;
c1r CLOB;
c2l CLOB;
c2m CLOB;
c2r CLOB;
c3l CLOB;
c3m CLOB;
c3r CLOB;
BEGIN
DBMS_LOB.CREATETEMPORARY( r, FALSE );
DBMS_LOB.CREATETEMPORARY( c1l, FALSE );
DBMS_LOB.CREATETEMPORARY( c1m, FALSE );
DBMS_LOB.CREATETEMPORARY( c1r, FALSE );
DBMS_LOB.CREATETEMPORARY( c2l, FALSE );
DBMS_LOB.CREATETEMPORARY( c2m, FALSE );
DBMS_LOB.CREATETEMPORARY( c2r, FALSE );
DBMS_LOB.CREATETEMPORARY( c3l, FALSE );
DBMS_LOB.CREATETEMPORARY( c3m, FALSE );
DBMS_LOB.CREATETEMPORARY( c3r, FALSE );
FOR i IN 1 .. 10 LOOP
DBMS_LOB.WRITEAPPEND( r, 4000, RPAD( 'y', 4000, 'y' ) );
DBMS_LOB.WRITEAPPEND( C1m, 20, RPAD( 'x', 20, 'x' ) );
DBMS_LOB.WRITEAPPEND( C1r, 40, RPAD( 'x', 40, 'x' ) );
DBMS_LOB.WRITEAPPEND( C2m, 200, RPAD( 'x', 200, 'x' ) );
DBMS_LOB.WRITEAPPEND( C2r, 400, RPAD( 'x', 400, 'x' ) );
DBMS_LOB.WRITEAPPEND( C3m, 2000, RPAD( 'x', 2000, 'x' ) );
DBMS_LOB.WRITEAPPEND( C3r, 4000, RPAD( 'x', 4000, 'x' ) );
END LOOP;
DBMS_LOB.WRITEAPPEND( c1l, 5, str );
DBMS_LOB.WRITEAPPEND( c1m, 5, str );
DBMS_LOB.WRITEAPPEND( c1r, 5, str );
DBMS_LOB.WRITEAPPEND( c2l, 5, str );
DBMS_LOB.WRITEAPPEND( c2m, 5, str );
DBMS_LOB.WRITEAPPEND( c2r, 5, str );
DBMS_LOB.WRITEAPPEND( c3l, 5, str );
DBMS_LOB.WRITEAPPEND( c3m, 5, str );
DBMS_LOB.WRITEAPPEND( c3r, 5, str );
FOR i IN 1 .. 10 LOOP
DBMS_LOB.WRITEAPPEND( C1l, 40, RPAD( 'x', 40, 'x' ) );
DBMS_LOB.WRITEAPPEND( C1m, 20, RPAD( 'x', 20, 'x' ) );
DBMS_LOB.WRITEAPPEND( C2l, 400, RPAD( 'x', 400, 'x' ) );
DBMS_LOB.WRITEAPPEND( C2m, 200, RPAD( 'x', 200, 'x' ) );
DBMS_LOB.WRITEAPPEND( C3l, 4000, RPAD( 'x', 4000, 'x' ) );
DBMS_LOB.WRITEAPPEND( C3m, 2000, RPAD( 'x', 2000, 'x' ) );
END LOOP;
INSERT INTO table_name VALUES ( NULL );
INSERT INTO table_name VALUES ( EMPTY_CLOB() );
INSERT INTO table_name VALUES ( '0123456789' );
INSERT INTO table_name VALUES ( str );
INSERT INTO table_name VALUES ( c1l );
INSERT INTO table_name VALUES ( c1m );
INSERT INTO table_name VALUES ( c1r );
INSERT INTO table_name VALUES ( c2l );
INSERT INTO table_name VALUES ( c2m );
INSERT INTO table_name VALUES ( c2r );
INSERT INTO table_name VALUES ( c3l );
INSERT INTO table_name VALUES ( c3m );
INSERT INTO table_name VALUES ( c3r );
INSERT INTO replacements VALUES ( str, r );
COMMIT;
END;
/
Query 1:
查询 1：
SELECT DBMS_LOB.GETLENGTH( value )
FROM table_name
结果：
| DBMS_LOB.GETLENGTH(VALUE) |
|---------------------------|
| (null) |
| 0 |
| 10 |
| 5 |
| 405 |
| 405 |
| 405 |
| 4005 |
| 4005 |
| 4005 |
| 40005 |
| 40005 |
| 40005 |
Query 2:
查询 2：
UPDATE table_name
SET value = LOB_REPLACE(
value,
( SELECT str FROM replacements ),
( SELECT repl FROM replacements )
)
Query 3:
查询 3：
SELECT DBMS_LOB.GETLENGTH( value )
FROM table_name
结果：
| DBMS_LOB.GETLENGTH(VALUE) |
|---------------------------|
| (null) |
| 0 |
| 10 |
| 40000 |
| 40400 |
| 40400 |
| 40400 |
| 44000 |
| 44000 |
| 44000 |
| 80000 |
| 80000 |
| 80000 |
回答by Anjali Sharma
Below function solves the problem:
下面的函数解决了这个问题：
create or replace FUNCTION replace_clob
(
in_source IN CLOB,
in_search IN VARCHAR2,
in_replace IN CLOB
)
RETURN CLOB
IS
l_pos pls_integer;
out_replace_clob CLOB := in_source;
BEGIN
l_pos := instr(in_source, in_search);
IF l_pos > 0 THEN
WHILE l_pos > 0 LOOP
out_replace_clob := substr(out_replace_clob, 1, l_pos-1)
|| in_replace
|| substr(out_replace_clob, l_pos+LENGTH(in_search));
l_pos := instr(out_replace_clob, in_search);
END LOOP;
RETURN out_replace_clob;
END IF;
RETURN in_source;
END replace_clob;
/
回答by idavid2013
This will do the job:
这将完成这项工作：
function CLOBREPLACE(
AINPUT CLOB,
APATTERN VARCHAR2,
ASUBSTITUTE CLOB
) return CLOB is
FCLOB CLOB := AINPUT;
FOFFSET INTEGER;
FCHUNK CLOB;
begin
if length(ASUBSTITUTE) > 32000 then
FOFFSET := 1;
FCLOB := replace(FCLOB, APATTERN, '###CLOBREPLACE###');
while FOFFSET <= length(ASUBSTITUTE) loop
FCHUNK := substr(ASUBSTITUTE, FOFFSET, 32000) || '###CLOBREPLACE###';
FCLOB := regexp_replace(FCLOB, '###CLOBREPLACE###', FCHUNK);
FOFFSET := FOFFSET + 32000;
end loop;
FCLOB := regexp_replace(FCLOB, '###CLOBREPLACE###', '');
else
FCLOB := replace(FCLOB, APATTERN, ASUBSTITUTE);
end if;
return FCLOB;
end;
Result of test case:
测试用例结果：
v2:32767
cl_small:32767
cl_big[1]:65535
cl_big[2]:98301
cl_big[3]:131069
回答by user2380723
Function REPLACE can be used for CLOB
函数 REPLACE 可用于 CLOB
Doc: http://psoug.org/reference/translate_replace.html
文档：http: //psoug.org/reference/translate_replace.html
Working sample:
工作样本：
declare
l_clob clob;
l_parname clob;
l_value clob;
l_par_id_obj varchar2(200) := '${id_obj}';
procedure setCLOBValue(p_CLOB in out CLOB, p_value string) as
begin
DBMS_LOB.createtemporary(p_CLOB, false);
dbms_lob.open(p_CLOB, dbms_lob.lob_readwrite);
dbms_lob.write(p_CLOB, length(p_value), 1, p_value);
dbms_lob.close(p_CLOB);
end;
begin
select SOURCE_FILE into l_clob from st_static_source_clob;
setCLOBValue(l_parname, l_par_id_obj);
setCLOBValue(l_value, '200701000024');
l_clob := replace(l_clob, l_parname, l_value);
end;
