We have got really interesting scenario for our companies busiest website. We want to replace work 'GCSE's' to 'GCSEs'. Now interesting thing is that text or data stores in CLOB columns of few Tables. Some how we manage to find the tables and it's CLOB column.
Then after we have used following two procedure to replace those text. And good thing about this script is that it's work for >32k length of column rows. In this scenario we calling SEARCH_N_REPLACE_CLOB procedure from SEARCH_PRO Procedure.
@@@@@@@@@@@@@@@ SEARCH_N_REPLACE_CLOB procedure @@@@@@@@@@@@@@@@@@@
create or replace
PROCEDURE search_n_replace_clob (dest_lob IN OUT CLOB,search_str VARCHAR2,replace_str VARCHAR2)
AS
temp_clob CLOB;
end_offset INTEGER := 1;
start_offset INTEGER := 1;
occurence NUMBER := 1;
replace_str_len NUMBER := LENGTH(replace_str);
temp_clob_len NUMBER := 0;
dest_lob_len NUMBER := 0;
BEGIN
IF dest_lob IS NULL THEN
RAISE_APPLICATION_ERROR(-20001, 'Destination LOB is empty');
END IF;
IF DBMS_LOB.ISOPEN(dest_lob) = 0 THEN
NULL;
END IF;
DBMS_LOB.CREATETEMPORARY(temp_clob,TRUE,DBMS_LOB.SESSION);
LOOP
end_offset := DBMS_LOB.INSTR(dest_lob,search_str,1,occurence);
IF end_offset = 0 THEN
temp_clob_len := DBMS_LOB.GETLENGTH(temp_clob);
dest_lob_len := DBMS_LOB.GETLENGTH(dest_lob) - start_offset + 1;
IF dest_lob_len > 0 THEN
DBMS_LOB.COPY(temp_clob,dest_lob,dest_lob_len,temp_clob_len+1,start_offset);
END IF;
EXIT;
END IF;
temp_clob_len := DBMS_LOB.GETLENGTH(temp_clob);
IF (end_offset - start_offset) > 0 THEN
DBMS_LOB.COPY(temp_clob,dest_lob,(end_offset - start_offset),temp_clob_len+1,start_offset);
END IF;
start_offset := end_offset + LENGTH(search_str);
occurence := occurence + 1;
IF replace_str IS NOT NULL THEN
DBMS_LOB.WRITEAPPEND(temp_clob,replace_str_len,replace_str);
END IF;
END LOOP;
IF LENGTH(search_str) > LENGTH(replace_str) OR LENGTH(replace_str) IS NULL THEN
DBMS_LOB.TRIM(dest_lob,DBMS_LOB.GETLENGTH(temp_clob));
END IF;
DBMS_LOB.COPY(dest_lob,temp_clob,DBMS_LOB.GETLENGTH(temp_clob),1,1);
END ;
@@@@@@@@@@@@@@@@ SEARCH_PRO Procedure @@@@@@@@@@@@@@@@@@@@@@@@@@
create or replace
PROCEDURE SEARCH_PRO AS
l_amt NUMBER DEFAULT 99900000;
l_length NUMBER :=0 ;
t_Clob CLOB;
--cursor c1 is select C from test FOR UPDATE;
--cursor c1 is select CUSTOM_VAL from test2 where custom_val like '%GCSE''s%' FOR UPDATE;
--cursor c1 is select CUSTOM_VAL from HEM.SQ_AST_ATTR_VAL where custom_val like '%GCSE''s%' FOR UPDATE;
--cursor c1 is select value from HEM.SQ_AST_MDATA_VAL where value like '%GCSE''s%' FOR UPDATE;
--cursor c1 is select XML from HEM.SQ_FNB_IDX where XML like '%GCSE''s%' FOR UPDATE;
--cursor c1 is select value from HEM.SQ_SCH_IDX where value like '%GCSE''s%' FOR UPDATE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO t_clob;
EXIT WHEN C1%NOTFOUND;
l_length := DBMS_LOB.GETLENGTH(T_CLOB);
if (l_amt > l_length) then
DBMS_OUTPUT.PUT_LINE(l_length);
search_n_replace_clob(T_CLOB,'GCSE''s','GCSEs');
--DBMS_OUTPUT.PUT_LINE(T_CLOB);
end if;
END LOOP;
--dest_lob CLOB;
--search_n_replace_clob(dest_lob,'GCSEs','GCSE''s');
-- dbms_output.put_line( to_char(dest_lob) ) ;
END SEARCH_PRO;
@@@@@@@@@@@@@@@@@@ EXECUTE PL/SQL Block @@@@@@@@@@@@@@@@@@@
set serveroutput on
DECLARE
BEGIN
--SELECT c INTO dest_lob FROM test WHERE key = 4 FOR UPDATE;
SEARCH_PRO;
--search_n_replace_clob(dest_lob,'GCSEs','GCSE''s');
END;
Then after we have used following two procedure to replace those text. And good thing about this script is that it's work for >32k length of column rows. In this scenario we calling SEARCH_N_REPLACE_CLOB procedure from SEARCH_PRO Procedure.
@@@@@@@@@@@@@@@ SEARCH_N_REPLACE_CLOB procedure @@@@@@@@@@@@@@@@@@@
create or replace
PROCEDURE search_n_replace_clob (dest_lob IN OUT CLOB,search_str VARCHAR2,replace_str VARCHAR2)
AS
temp_clob CLOB;
end_offset INTEGER := 1;
start_offset INTEGER := 1;
occurence NUMBER := 1;
replace_str_len NUMBER := LENGTH(replace_str);
temp_clob_len NUMBER := 0;
dest_lob_len NUMBER := 0;
BEGIN
IF dest_lob IS NULL THEN
RAISE_APPLICATION_ERROR(-20001, 'Destination LOB is empty');
END IF;
IF DBMS_LOB.ISOPEN(dest_lob) = 0 THEN
NULL;
END IF;
DBMS_LOB.CREATETEMPORARY(temp_clob,TRUE,DBMS_LOB.SESSION);
LOOP
end_offset := DBMS_LOB.INSTR(dest_lob,search_str,1,occurence);
IF end_offset = 0 THEN
temp_clob_len := DBMS_LOB.GETLENGTH(temp_clob);
dest_lob_len := DBMS_LOB.GETLENGTH(dest_lob) - start_offset + 1;
IF dest_lob_len > 0 THEN
DBMS_LOB.COPY(temp_clob,dest_lob,dest_lob_len,temp_clob_len+1,start_offset);
END IF;
EXIT;
END IF;
temp_clob_len := DBMS_LOB.GETLENGTH(temp_clob);
IF (end_offset - start_offset) > 0 THEN
DBMS_LOB.COPY(temp_clob,dest_lob,(end_offset - start_offset),temp_clob_len+1,start_offset);
END IF;
start_offset := end_offset + LENGTH(search_str);
occurence := occurence + 1;
IF replace_str IS NOT NULL THEN
DBMS_LOB.WRITEAPPEND(temp_clob,replace_str_len,replace_str);
END IF;
END LOOP;
IF LENGTH(search_str) > LENGTH(replace_str) OR LENGTH(replace_str) IS NULL THEN
DBMS_LOB.TRIM(dest_lob,DBMS_LOB.GETLENGTH(temp_clob));
END IF;
DBMS_LOB.COPY(dest_lob,temp_clob,DBMS_LOB.GETLENGTH(temp_clob),1,1);
END ;
@@@@@@@@@@@@@@@@ SEARCH_PRO Procedure @@@@@@@@@@@@@@@@@@@@@@@@@@
create or replace
PROCEDURE SEARCH_PRO AS
l_amt NUMBER DEFAULT 99900000;
l_length NUMBER :=0 ;
t_Clob CLOB;
--cursor c1 is select C from test FOR UPDATE;
--cursor c1 is select CUSTOM_VAL from test2 where custom_val like '%GCSE''s%' FOR UPDATE;
--cursor c1 is select CUSTOM_VAL from HEM.SQ_AST_ATTR_VAL where custom_val like '%GCSE''s%' FOR UPDATE;
--cursor c1 is select value from HEM.SQ_AST_MDATA_VAL where value like '%GCSE''s%' FOR UPDATE;
--cursor c1 is select XML from HEM.SQ_FNB_IDX where XML like '%GCSE''s%' FOR UPDATE;
--cursor c1 is select value from HEM.SQ_SCH_IDX where value like '%GCSE''s%' FOR UPDATE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO t_clob;
EXIT WHEN C1%NOTFOUND;
l_length := DBMS_LOB.GETLENGTH(T_CLOB);
if (l_amt > l_length) then
DBMS_OUTPUT.PUT_LINE(l_length);
search_n_replace_clob(T_CLOB,'GCSE''s','GCSEs');
--DBMS_OUTPUT.PUT_LINE(T_CLOB);
end if;
END LOOP;
--dest_lob CLOB;
--search_n_replace_clob(dest_lob,'GCSEs','GCSE''s');
-- dbms_output.put_line( to_char(dest_lob) ) ;
END SEARCH_PRO;
@@@@@@@@@@@@@@@@@@ EXECUTE PL/SQL Block @@@@@@@@@@@@@@@@@@@
set serveroutput on
DECLARE
BEGIN
--SELECT c INTO dest_lob FROM test WHERE key = 4 FOR UPDATE;
SEARCH_PRO;
--search_n_replace_clob(dest_lob,'GCSEs','GCSE''s');
END;
