Replace Keyword in CLOB Column

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;

RMAN check all the backup in one go

Do the following from RMAN while connected to the target. If the preview fail then so will the duplication. 

Do the following so we will know your RMAN configuration. 

To get the right time of the backup you have to make sure NLS date is set. 

If using csh 

setenv NLS_DATE_FORMAT 'DD-MON-YYYY HH24:MI:SS' 

If using ksh use: 

export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS' 

RMAN >>@RMAN_CHECK.RCV ( MAKE FILE OF BELOW CONTENT )

spool log to preview.log; 
set echo on; 

show all; 

list incarnation of database; 

run { 
set until time "to_date('25-09-2014 20:00:00','DD-MM-YYYY HH24:MI:SS')"; 
allocate channel d1 type='sbt_tape'; 
restore preview database controlfile spfile;} 


list backup summary; 

list backup of database archivelog all; 

list backup of controlfile; 

list archivelog all; 

report schema; 

list copy of database; 

-- Upload preview.log 


==============================
RMAN 11GR2 : DUPLICATE Without Target And Recovery Catalog Connection ( Doc ID 874352.1 )