I have a table in Oracle database with field with data type CLOB. The name of field is XMLString. I am storing XML string which is 10,000 characters long for each record. I have more than 100, 000 of records in this table.
I need to update segment of the XML string on each record at specific place. For example I need to update each record at 14th position with some string like "My New text". This replacement text is 11 characters long. So this simply means it will replace 11 characers starting from 14th character.
I tried to use DBMS_LOB.FRAGMENT_REPLACE, but it is not exactly what I wanted.
Is there any simple command like
Replace(XMLString, 14, ‘My New text’)
so that I can do something like below?
UPDATE MYTABLE
SET MyClobField = Replace(MyClobField, 14, 'My New text')
WHERE MyTableID>5000
Any help would be appreciated.
Answer
SQL using
UPDATE MYTABLE
SET MyClobField = substr(MyClobField, 1, 10) || to_clob('MyNewtext')||substr(MyClobField, 10+length('MyNewtext')+1)
where..
just change the 2 occurances of "10" to the offset.
or in PL/SQL like this using the DBMS_LOB.WRITE API (this is faster than the above)
SQL> create table foo(c clob);
Table created.
SQL> insert into foo values ( 'this is a test string ' || rpad('x', 20, 'x'));
1 row created.
SQL> commit;
Commit complete.
SQL> select * from foo;
C
--------------------------------------------------------------------------------
this is a test string xxxxxxxxxxxxxxxxxxxx
SQL> declare
2 v_lob clob;
3 begin
4
5 for r_lob in (select c
6 from foo
7 for update)
8 loop
9 dbms_lob.write(r_lob.c, 6, 16, 'phrase'); -- ie write at offset 16, 6 bytes
10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed.
SQL> select * from foo;
C
--------------------------------------------------------------------------------
this is a test phrase xxxxxxxxxxxxxxxxxxxx