Updating Clob Field
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Updating Clob Field

  1. #1
    Join Date
    Mar 2002
    Posts
    25
    Can anyone tell me how to update/alter some text that is held in a clob column? I can display the text but when I edit it in Toad I get the error ORA-01036 illegal variable name/number - what does that mean and how do I get round it?

    Thanks.

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    By using DBMS_LOB. Send me an email if you do not know how to use DBMS_LOB.

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Mar 2002
    Posts
    25
    I'm sorry no I don't know how to use DBMS_LOB - can you advise?

    Thanks, Ali

  4. #4
    Join Date
    Mar 2002
    Posts
    25
    Still need help on how to use DBMS_LOB specifically for updating text in an existing clob column.

    Anyone help?????

    Thanks, Ali

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by abutler
    Still need help on how to use DBMS_LOB specifically for updating text in an existing clob column.

    Anyone help?????

    Thanks, Ali
    OK, ok :-)

    Here is what you do.
    Assume that you have a table called lob_taulu:

    Code:
    SQL> desc lob_taulu
     Name                            Null?    Type
     ------------------------------- -------- ----------------------
     KEY                                      NUMBER
     C                                        CLOB
    
    SQL> 
    
    SQL> insert into lob_taulu values(1,'The football team Liverpool are known as the Red Devils');
    
    1 row created.
    
    SQL> commit work;
    
    Commit complete.
    
    SQL>
    Now, we notice that we made a mistake. Liverpoool should be replaced with Manchester United. In other words, we need to make an update on a CLOB field.

    I run the following block:

    Code:
    SQL> declare
      2  lob_loc clob;
      3  begin
      4  select c into lob_loc from lob_taulu where key = 1 for update;
      5  search_n_replace_clob(lob_loc,'Liverpool','Manchester United');
      6  end;
      7  /
    
    PL/SQL procedure successfully completed.
    
    SQL>
    SQL> select C from lob_taulu;
    
    C
    ----------------------------------------------------------------
    The football team Manchester United are known as the Red Devils
    Here search_n_replace_clob is the following procedure thanks to Metalink:

    Code:
    PROCEDURE search_n_replace_clob (lob_loc 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;
    lob_loc_len NUMBER := 0;
    BEGIN
    DBMS_LOB.CREATETEMPORARY(temp_clob,TRUE,DBMS_LOB.SESSION);
    LOOP
    end_offset := DBMS_LOB.INSTR(lob_loc,search_str,1,occurence);
    IF end_offset = 0 THEN
    temp_clob_len := DBMS_LOB.GETLENGTH(temp_clob);
    lob_loc_len := DBMS_LOB.GETLENGTH(lob_loc) - start_offset + 1;
    IF lob_loc_len > 0 THEN
    DBMS_LOB.COPY(temp_clob,lob_loc,lob_loc_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,lob_loc,(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) THEN
    DBMS_LOB.TRIM(lob_loc,DBMS_LOB.GETLENGTH(temp_clob));
    END IF;
    DBMS_LOB.COPY(lob_loc,temp_clob,DBMS_LOB.GETLENGTH(temp_clob),1,1);
    END ;
    Is it clear?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  6. #6
    Join Date
    Mar 2002
    Posts
    25
    Thanks Julian that worked a treat!

    Not very good with pl/sql yet so it does help when I get pointed in the right direction, although understanding what it was doing will take me a little longer to work out!!!


    Thanks again.

    Ali


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