Updating CLOB field in Oracle

Question

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 1

SQL using

UPDATE MYTABLE 
SET MyClobField = substr(MyClobField, 1, 10) || to_clob('MyNewtext')||substr(MyClobField, 10+length('MyNewtext')+1)
where..

just change the 2 occurrences 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

Answer 2

Try this :-

update table set column = replace(column,'hello','abcded')

Answer 3

try this for usefull solution. this script updates the string between two strings. for instance you have one unique tag in your xml file. you can change this tag value like below. if you have more than one tag that you wanted to change than yo need to edit this script. because this script finds only first equality.

UPDATE table_name s
   SET s.clobcolumn = substr(s.clobcolumn, 1, to_number(dbms_lob.instr(s.clobcolumn, '<firststring>')) + length('<firststring>')-1) || 'replacestring' ||
                   substr(s.clobcolumn, to_number(dbms_lob.instr(s.clobcolumn, '</secondstring>')) , length(s.clobcolumn))
 WHERE ...
   AND ...
   AND ...;

SELECT to_char(substr(s.clobcolumn,
                      dbms_lob.instr(s.clobcolumn, '<firststring>'),
                      (dbms_lob.instr(s.clobcolumn, '</secondstring>') + length('</secondstring>')) - dbms_lob.instr(s.clobcolumn, '<firststring>'))) replaced_string
  FROM table_name s
 WHERE ...
   AND ...
   AND ...;

Answer 4

Try this :-

DECLARE
      str varchar2(32767);
    BEGIN
      str := '<big-string>';
      update <table-name> set <col-name-1>= str where <col-name-2>= ;
    END;