Printable View
I have a CLOB field in a table and I need to repace any occurance of 'string1' with 'string2'. I cant be sure where in the CLOB it will be or how many times it will be in there. Any help would be great as I am having a few issues getting it resolved.
Many thanks
Hi
I recently had a similar problem and found the link below to be useful. It mimics the replace function for clob columns.
http://www.astral-consultancy.co.uk/...doco.cgi?11080
Hope this helps
I wrote this and it seems to work. Im worried that what this does though is just change it to a string. Im still testing it but if someone knows this is defo wrong then please tell me.
Many thanks for reply.
DECLARE
v_string_origional VARCHAR2(100);
v_string_new VARCHAR2(100);
v_table_name VARCHAR2(150);
v_field_name VARCHAR2(150);
v_clob CLOB;
v_clob2 CLOB;
v_row_id VARCHAR2(100);
str VARCHAR2(2000);
str2 VARCHAR2(2000);
v_res varchar2(1000);
BEGIN
v_string_origional := 'old string';
v_string_new := 'replacement string;
v_table_name := 'temp';
v_field_name := 'field1';
v_row_id := 'rowid1';
str := 'select ' || v_field_name || ' from ' || v_table_name || ' where rowid = ''' || v_row_id || '''';
EXECUTE IMMEDIATE str INTO v_clob;
v_clob2 := REPLACE(v_clob, v_string_origional, v_string_new);
str2 := 'update ' || v_table_name || ' set ' || v_field_name || ' = '''||v_clob2||''' where rowid = ''' || v_row_id || '''';
EXECUTE IMMEDIATE str2;
END;
You should definitely use the dbms_lob package.
Sorry to be pain but why? Im not disagreeing I would just like to know why. and, if you have time would it be possible to show me how I could modify my code to use it.
Much appreciated! it all seems a bit of a dark art right now.
Scary.Code:str2 := 'update ' || v_table_name || ' set ' || v_field_name || ' = '''||v_clob2||''' where rowid = ''' || v_row_id || '''';
EXECUTE IMMEDIATE str2;
If that v_clob2 value contains single quotes and double dashes you could end up executing something like
update table set value ='I'--blah blah where rowid=....
and seriously screw up your database.
Even if you don't, using this sort of construct (dynamic sql without bind variables) can play havoc with your shared pool.
Thanks for the warning.
Do you know how I can replace that with something better?
How can I get a table name that is a string used in an update statement with out turning the whole thing into a string and executing it?
ie:
how can I do this:
update 'schema1.table1' set 'field1' = 'value';
It complains that the table doesnt exist due it being a string rather than a table name. This is the only reason I am using it as I am.
The code in this example is faulty. Try to reduce the work amount from 32767 to 5 (to better visualize the error), and set the following values:Quote:
Originally Posted by rikfair
p_clob '123456789012345678901234567890'
p_what '7'
p_with 'A'
The result will be '1234556A8901233456AA8901234556A890' - not quite correct as the last character of each part is repeated.
Sorry for the double post, but this code will even ignore text to replace that spans over two 32k segments.Quote:
Originally Posted by rikfair
don't worry, if poster waited for almost three years he/she could afford a double post :)Quote:
Originally Posted by Bowser