DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Replacing a string within a CLOB - Oracle 9i

  1. #1
    Join Date
    Jan 2007
    Posts
    7

    Replacing a string within a CLOB - Oracle 9i

    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

  2. #2
    Join Date
    Jan 2007
    Posts
    5
    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

  3. #3
    Join Date
    Jan 2007
    Posts
    7

    how about this?

    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;

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    You should definitely use the dbms_lob package.

  5. #5
    Join Date
    Jan 2007
    Posts
    7

    why?

    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.

  6. #6
    Join Date
    Feb 2005
    Posts
    158
    Code:
    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.

  7. #7
    Join Date
    Jan 2007
    Posts
    7
    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.

  8. #8
    Join Date
    Dec 2009
    Posts
    2

    Thumbs down

    Quote Originally Posted by rikfair View Post
    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.

  9. #9
    Join Date
    Dec 2009
    Posts
    2

    Thumbs down

    Quote Originally Posted by rikfair View Post
    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.

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by Bowser View Post
    Sorry for the double post
    don't worry, if poster waited for almost three years he/she could afford a double post
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width