Updating clob in pl sql
June 19, 2002 - pm UTC In my example, I did an insert then then modified it. I have tried the code you mentioned above about replacing a string with a string in clob fields. Tom I tried this and have some problems Need your help SOS I have a situation where multiple entries of the string needs to be replaced and that string can be at the end I did try my LEVEL BEST and at the end resorted to trial & error create or replace procedure usk_clob_replace ( p_lob in out clob, p_what varchar2, p_with varchar2) is n number; x number; dupe_i number; type tabtype is table of number index by binary_integer; tab1 tabtype; begin for i in 1..dbms_lob.getlength(p_lob)/2 loop tab1(i) := dbms_lob.instr( p_lob, p_what,1,i ); exit when tab1(i) =0; end loop; x:=tab1.count ; dbms_output.put_line(x) ; for i in 1..tab1.count loop dupe_i :=i; if ( nvl(tab1(i) ,0) length(p_with) ) then dbms_lob.trim( p_lob, dbms_lob.getlength(p_lob)-(length(p_what)-length(p_with)) ); end if; end if; end loop; exception when others then --ORA-22994: --source offset is beyond the end of the source LOB dbms_lob.writeappend(p_lob, length(p_with) length(p_what) , rpad( ' ', length(p_with) length(p_what),' ')); dbms_lob.copy( p_lob,p_lob, dbms_lob.getlength(p_lob) , tab1(dupe_i) length(p_with) , tab1(dupe_i) length(p_what) ); dbms_lob.write( p_lob, length(p_with), tab1(dupe_i), p_with ); end; create table t ( x clob); insert into t values ( 'abc abc abc'); declare l_lob clob; begin select * into l_lob from t for update; usk_clob_replace ( l_lob,'213', 'aa'); end; SQLI think this does it -- test it fully, did it rather quick.
I had the row locked (since I inserted it and didn't commit yet) You are probably trying this on an existing row. And it works on a few rows I have tested it on - but I get an error ORA-22994 "source offset is beyond the end of the source LOB" when I run it on the whole table. create or replace procedure lob_replace( p_lob in out clob, p_what in varchar2, p_with in varchar2 ) as n number; l_offset number := 1; begin loop n := dbms_lob.instr( p_lob, p_what, l_offset ); if ( nvl(n,0) length(p_with) ) then dbms_lob.trim( p_lob, dbms_lob.getlength(p_lob)-(length(p_what)-length(p_with)) ); end if; l_offset := l_offset length(p_with); else exit; end if; end loop; end; / [email protected] Sorry, I cannot make a small testcase for this, it appears hard to create perfect preconditions. Kyte's) routine: dbms_lob.copy( p_lob, p_lob, dbms_lob.getlength(p_lob), n p_with_length, n p_what_length ); fails to work properly if (? ) when the what's or with's length is large enough.
Say you wanted to modify the 1234 on the block with the caret ^ under it.
[email protected] A LOB works just like an OS file does in this regards. 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.
If you open a file for write/append -- you can seek around the file all over the place. A lob is just like that -- you have to rewrite the entire end of the lob.
10 6 loop 7 dbms_lob.write Append( l_clob, 32000, rpad( '*', 32000, '*' ) ); 8 end loop; 9 dbms_lob.write Append( l_clob, 5, '[email protected]@' ); 10 commit; 11 end; 12 / PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2 select dbms_lob.getlength(x), substr( x, dbms_lob.getlength(x)-10 ) from t; DBMS_LOB.