Hello Guys
I am working on a requirement listed below wherein some values in the clob cloumn have to be updated for lot of records.
--- Sample data code---
create table test_replace (pid number, p_clob clob);
begin
for i in 1..10 loop
insert into test_replace values(i,dbms_xmlgen.getxml('select * from all_objects where rownum < 1000'));
end loop;
end;
/
----------------------------------------------
Now the p_clob column of all the records in the table needs to be updated per below. The below merge statement will replace only one value for the matching records, but i need to update multiple values on a single record. e.g. not only the <STATUS> but <OBJECT_NAME>,<OBJECT_ID> etc. The number of values to be updated are same for every record. How can i acheive this the most performant way. I tried to make a function and pass the clob, and all the values ,but calling this for every record involves switching context and takes a lot of time. Kindly suggest
merge into test_replace tr
using (select 1 pid from dual union select 2 pid from dual) src
on( tr.pid=src.pid)
when matched then
update set tr.p_clob = replace (p_clob,'<STATUS>VALID</STATUS>','<STATUS>NEW STATUS</STATUS>')