If you are looking for oracle query to find and replace a string in clob column in bulk. You can find same tip below:
Create table having clob column.
create table MyClobTable ( column1 int, clob_column clob );
|
Create procedure to perform insertion in table
create or replace procedure MyProc( proc_column1 in int, proc_text in
varchar2 )
as
begin
insert into MyClobTable values ( proc_column1, proc_text );
end;
|
Insert below two records in table.
exec MyProc(1, 'I am Narendra Verma and currently living in Atlanta. I visited a lot of places in
Atlanta. Atlanta is a nice city in US.' );
exec MyProc(2, It is a great time to be in the City of Atlanta' );
commit;
|
Check if rows are inserted.
select * from MyClobTable;
|
Output:
1
|
I am Narendra Verma and currently living in Atlanta. I visited a lot of places in Atlanta. Atlanta is a
nice city in US.
|
2
|
It is a great time to be in the City of Atlanta
|
Execute below to replace ‘Atlanta’ with 'Alpharetta' in all
rows.
MERGE INTO MyClobTable A
USING (SELECT column1,
TO_CLOB
(REPLACE (clob_column, 'Atlanta', 'Alpharetta'))
AS
updated_string
FROM MyClobTable
WHERE clob_column
LIKE '%Atlanta%'
) B
ON (A.column1 = B.column1)
WHEN MATCHED
THEN
UPDATE SET A.clob_column =
B.updated_string;
|
Check if 'Atlanta' word is replaced with 'Alpharetta' in all rows.
select * from MyClobTable;
|
Output:
1
|
I am Narendra Verma and currently living in Alpharetta. I visited a lot of places in Alpharetta. Alpharetta is a nice city in US.
|
2
|
It is a great time to be in the City of Alpharetta
|
