Update April 2025: See Piotr's comment for an amended version of this function.

Well, an overwrite is a replace, what you want is a "global change".

Think of the lob as a big stream of characters like this:

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
^

each x represents a database block. Say you wanted to modify the 1234 on the block with the caret ^ under it. That block would have to have all of its data pushed down by 6 bytes. That would push the last 6 bytes off of each subsequent block into the head of the next block. You are rewriting the end of the entire lob -- from that position onwards.

You can do this with dbms_lob.copy - but it will involve rewriting the ENTIRE end of the lob:

ops$tkyte@DEV816> create table t ( x clob );

Table created.

ops$tkyte@DEV816>
ops$tkyte@DEV816>
ops$tkyte@DEV816> create or replace
2 procedure lob_replace( p_lob in out clob,
3 p_what in varchar2,
4 p_with in varchar2 )
5 as
6 n number;
7 begin
8 n := dbms_lob.instr( p_lob, p_what );
9 if ( nvl(n,0) > 0 )
10 then
11 dbms_lob.copy( p_lob,
12 p_lob,
13 dbms_lob.getlength(p_lob),
14 n+length(p_with),
15 n+length(p_what) );
16
17 dbms_lob.write( p_lob, length(p_with), n, p_with );
18 if ( length(p_what) > length(p_with) )
19 then
20 dbms_lob.trim( p_lob,
21 dbms_lob.getlength(p_lob)-(length(p_what)-length(p_with)) );
22 end if;
23 end if;
24 end;
25 /

Procedure created.

ops$tkyte@DEV816>
ops$tkyte@DEV816> declare
2 l_lob clob;
3 begin
4 insert into t values ( 'Hello 1234 World' ) returning x into l_lob;
5 lob_replace( l_lob, '1234', '1234567890' );
6 end;
7 /

PL/SQL procedure successfully completed.

ops$tkyte@DEV816>
ops$tkyte@DEV816> select * from t;

X
--------------------------------------------------------------------------------
Hello 1234567890 World

ops$tkyte@DEV816>
ops$tkyte@DEV816> begin
2 for x in ( select * from t ) loop
3 lob_replace( x.x, '1234567890', '1234' );
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

ops$tkyte@DEV816> select * from t;

X
--------------------------------------------------------------------------------
Hello 1234 World

ops$tkyte@DEV816>
A LOB works just like an OS file does in this regards. If you open a file for write/append -- you can seek around the file all over the place. You can write random bytes anywhere in the file. That simply overwrites the bytes that were there. If you needed to change the 1234 into 1234567890 in the middle of a file -- you have to AT LEAST rewrite the entire end of the file to open up a 6 byte slot for the extra characters. A lob is just like that -- you have to rewrite the entire end of the lob.