Wednesday 12 November 2014

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;

No comments: