Skip to content

Instantly share code, notes, and snippets.

@daithiocrualaoich

daithiocrualaoich/gist:1197348

Created September 6, 2011 11:49
Show Gist options
  • Save daithiocrualaoich/1197348 to your computer and use it in GitHub Desktop.
Save daithiocrualaoich/1197348 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.sql
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;
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment