CODEWITHSUNDEEP
oracle-databaseclob
Shai
Shai

Reputation: 569

Updating CLOB field in Oracle

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.

Upvotes: 6

Views: 129547

Answers (5)

r0tt
r0tt

Reputation: 379

In SQLDeveloper you can edit CLOBs by clicking on the schema tables and select edit. In the data tab you can generate the update statements for a clob.

Upvotes: 0

DazzaL
DazzaL

Reputation: 21973

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

Upvotes: 9

Yunus Emre ELAGÖZ
Yunus Emre ELAGÖZ

Reputation: 1

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 ...;

Upvotes: 0

venkatj
venkatj

Reputation: 1

Try this :-

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

Upvotes: -1

Vijay
Vijay

Reputation: 5010

Try this :-

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

Upvotes: 0

Related Questions