How To Replicate the Replace Built-In for CLOBs
A HunBug Document
Document No.:
11080
Database Version: Oracle 9i
Last Updated: 01 Jan 2006
Author: HunBug
Database Version: Oracle 9i
Last Updated: 01 Jan 2006
Author: HunBug
The DBMS_LOB package is very useful but doesn’t come
with a replace function. The standard REPLACE built-in is restricted by
the PLSQL limit of a VARCHAR2, making it unusable for CLOB data
Examples can be found to perform a replace using a procedure, but these can’t simply be converted to a function as the DBMS_LOB.WRITE procedure can’t be called from a select statement.
The solution here has to avoid these procedures so it can be used as a proper function. Therefore it loops through the CLOB, 32K each time as this is the maximum DBMS_LOB.SUBSTR can return.
The Function
How It Works
The function accepts three parameters, the same as the standard REPLACE function.
1. p_clob: The CLOB data
2. p_what: The search string
3. p_with: The replacement string
It first checks that the p_what string is not null, to save time checking the CLOB.
The function then begins the first of two loops.
The inner loop then checks for the search string, p_what, in the segment of CLOB that is current being processed.
Examples can be found to perform a replace using a procedure, but these can’t simply be converted to a function as the DBMS_LOB.WRITE procedure can’t be called from a select statement.
The solution here has to avoid these procedures so it can be used as a proper function. Therefore it loops through the CLOB, 32K each time as this is the maximum DBMS_LOB.SUBSTR can return.
The Function
CREATE OR REPLACE FUNCTION dfn_clobReplace
( p_clob IN CLOB,
p_what IN VARCHAR2,
p_with IN VARCHAR2 ) RETURN CLOB IS
c_whatLen CONSTANT PLS_INTEGER := LENGTH(p_what);
c_withLen CONSTANT PLS_INTEGER := LENGTH(p_with);
l_return CLOB;
l_segment CLOB;
l_pos PLS_INTEGER := 1-c_withLen;
l_offset PLS_INTEGER := 1;
BEGIN
IF p_what IS NOT NULL THEN
WHILE l_offset < DBMS_LOB.GETLENGTH(p_clob) LOOP
l_segment := DBMS_LOB.SUBSTR(p_clob,32767,l_offset);
LOOP
l_pos := DBMS_LOB.INSTR(l_segment,p_what,l_pos+c_withLen);
EXIT WHEN (NVL(l_pos,0) = 0) OR (l_pos = 32767-c_withLen);
l_segment := TO_CLOB( DBMS_LOB.SUBSTR(l_segment,l_pos-1)
||p_with
||DBMS_LOB.SUBSTR(l_segment,32767-c_whatLen-l_pos-c_whatLen+1,l_pos+c_whatLen));
END LOOP;
l_return := l_return||l_segment;
l_offset := l_offset + 32767 - c_whatLen;
END LOOP;
END IF;
RETURN(l_return);
END;
/
( p_clob IN CLOB,
p_what IN VARCHAR2,
p_with IN VARCHAR2 ) RETURN CLOB IS
c_whatLen CONSTANT PLS_INTEGER := LENGTH(p_what);
c_withLen CONSTANT PLS_INTEGER := LENGTH(p_with);
l_return CLOB;
l_segment CLOB;
l_pos PLS_INTEGER := 1-c_withLen;
l_offset PLS_INTEGER := 1;
BEGIN
IF p_what IS NOT NULL THEN
WHILE l_offset < DBMS_LOB.GETLENGTH(p_clob) LOOP
l_segment := DBMS_LOB.SUBSTR(p_clob,32767,l_offset);
LOOP
l_pos := DBMS_LOB.INSTR(l_segment,p_what,l_pos+c_withLen);
EXIT WHEN (NVL(l_pos,0) = 0) OR (l_pos = 32767-c_withLen);
l_segment := TO_CLOB( DBMS_LOB.SUBSTR(l_segment,l_pos-1)
||p_with
||DBMS_LOB.SUBSTR(l_segment,32767-c_whatLen-l_pos-c_whatLen+1,l_pos+c_whatLen));
END LOOP;
l_return := l_return||l_segment;
l_offset := l_offset + 32767 - c_whatLen;
END LOOP;
END IF;
RETURN(l_return);
END;
/
How It Works
The function accepts three parameters, the same as the standard REPLACE function.
1. p_clob: The CLOB data
2. p_what: The search string
3. p_with: The replacement string
It first checks that the p_what string is not null, to save time checking the CLOB.
The function then begins the first of two loops.
WHILE l_offset <
DBMS_LOB.GETLENGTH(p_clob) LOOP
l_segment := DBMS_LOB.SUBSTR(p_clob,32767,l_offset);
The outer loop splits the main CLOB into segments of 32K, so that the
inner loop can process the data.l_segment := DBMS_LOB.SUBSTR(p_clob,32767,l_offset);
The inner loop then checks for the search string, p_what, in the segment of CLOB that is current being processed.
l_pos := DBMS_LOB.INSTR(l_segment,p_what,l_pos+c_withLen);
EXIT WHEN (NVL(l_pos,0) = 0) OR (l_pos = 32767-c_withLen);
If the search string is not found it exits the loop to process the next
segment. If the search string is found at the very end of the string,
it moves on to the next segment to be processed, as the segments
overlap by the length of the search string, so a search string
occurring over a segment break does not get missed.EXIT WHEN (NVL(l_pos,0) = 0) OR (l_pos = 32767-c_withLen);
l_segment := TO_CLOB( DBMS_LOB.SUBSTR(l_segment,l_pos-1)
||p_with
||DBMS_LOB.SUBSTR(l_segment,32767-c_whatLen-l_pos-c_whatLen+1,l_pos+c_whatLen));
The segment is then rebuilt, from the data before the search string,
the replacement string, then the data after the search string.||p_with
||DBMS_LOB.SUBSTR(l_segment,32767-c_whatLen-l_pos-c_whatLen+1,l_pos+c_whatLen));
l_return :=
l_return||l_segment;
l_offset := l_offset + 32767 - c_whatLen;
The processed segment is then added to the return CLOB and the offset
for the segment is increased by 32K minus the search string length.l_offset := l_offset + 32767 - c_whatLen;
