Updating clob data fildating com
Do we need to change the NLS or database character set or is this another problem?
I have done some research, but have yet to find a solution. I have never seen this happen with the application of a patchset.
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.
Hi, I have a table say Table Table_A which has a clob data column column_A. In Toad you can open your table in the schema browser, double click on the CLOB cell in the data tab and it will bring up a window to edit the clob contents you can also use the Toad keyword "edit" on the sql editor use it like you would a select statement edit your_table where ....But it does not change anything in the clob field of oracle.Example: Writing CLOB Data Use the set Character Stream method or the set Ascii Stream method to write data to a CLOB.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.
[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.