Replacing a string within a CLOB - Oracle 9i

[jgrobler]

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

[rikfair]

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

[jgrobler]

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;

[gandolf989]

You should definitely use the dbms_lob package.

[jgrobler]

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.

[gamyers]

str2 := 'update ' || v_table_name || ' set ' || v_field_name || ' = '''||v_clob2||''' where rowid = ''' || v_row_id || '''';
EXECUTE IMMEDIATE str2;

Scary.
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.

[jgrobler]

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.

[Bowser]

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

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:
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.

[Bowser]

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

Sorry for the double post, but this code will even ignore text to replace that spans over two 32k segments.

[PAVB]

Sorry for the double post

don't worry, if poster waited for almost three years he/she could afford a double post