Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Unanswered: Appending text to an existing data row?

    Hi!

    I have a table with logs, I was wondering if it was possible to append data to my clob fields in existing rows? We are talking rather large amounts of data, so I cannot just retrieve the data, append to it in code and repost it (my Oracle 8i driver has a 4000 char limit).

    In pseudo SQL what I want to do is the following:

    UPDATE tblLog SET cLog = (cLog + 'new logtext') WHERE id = 1

    Thanks in advance

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: Appending text to an existing data row?

    Originally posted by Zellez
    Hi!

    I have a table with logs, I was wondering if it was possible to append data to my clob fields in existing rows? We are talking rather large amounts of data, so I cannot just retrieve the data, append to it in code and repost it (my Oracle 8i driver has a 4000 char limit).

    In pseudo SQL what I want to do is the following:

    UPDATE tblLog SET cLog = (cLog + 'new logtext') WHERE id = 1

    Thanks in advance

    UPDATE tblLog SET cLog = cLog||'new logtext' WHERE id = 1

  3. #3
    Join Date
    Feb 2004
    Posts
    7
    Thank you for your answer, I tried to do as you suggested, but I get an "inconsistent datatypes" error msg. Does this have something to do with me using a CLOB field? or is there something else I am doing horribly wrong

    --------------
    update actionlog set actionmemo = (actionmemo||'test') where id = 1
    *
    ERROR on line 1:
    ORA-00932: inkonsistente datatyper (translated: Inconsistent datatypes)
    --------------

  4. #4
    Join Date
    Feb 2004
    Posts
    7
    Small bump, I really need to get this working

    Any suggestion appreciated, I can even change the field setup if nessecary, currently the field in question is a clob field.

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    SQL> create table c
    2 (
    3 var CLOB
    4 );

    Table created.

    SQL> insert into c values ('Test');

    1 row created.

    SQL> update c set var = var|| ' 2';

    1 row updated.

    SQL> select * from c;

    VAR
    ------------------------------------
    Test 2
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Feb 2004
    Posts
    7

    Angry

    Thanx for the quick reply,

    I do the EXACT same thing as you do r123456 (created table c with var CLOB etc.), but still get the "inconsistent datatype" error. I'll have a look on my database setup next, maybe the error is there .. rather annoying this.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Zellez
    Thanx for the quick reply,

    I do the EXACT same thing as you do r123456 (created table c with var CLOB etc.), but still get the "inconsistent datatype" error. I'll have a look on my database setup next, maybe the error is there .. rather annoying this.
    Or perhaps more likely it is a database version issue?

  8. #8
    Join Date
    Feb 2004
    Posts
    7
    Originally posted by andrewst
    Or perhaps more likely it is a database version issue?
    Like Oracle 9 <> Oracle 8? The thought crossed my mind, that would leave me with a problem tho, what use are a 2GB CLOB field if you cannot put more than 4k chars in it, and cannot append to it later?

    My current approach is using SQL, maybe I have to code some oracle specific procedure to be able to accomplish my goal? (I am rather new to oracle, and SQL server doesn't even break a sweat doing this trough SQL).

    The problem is, I have data which are much more than 4k chars in size. It would be really nice to be able to sqeeze them into that CLOB field, and preferrably be able to get them back out

    Open for suggestions

  9. #9
    Join Date
    Feb 2004
    Posts
    1
    try DBMS_LOB.APPEND()

  10. #10
    Join Date
    Feb 2004
    Posts
    7
    Thank you all for your suggestions and pointers

    I ended up creating a procedure which I call from the program, feeding it with 4k characters a time, appending to the actionmemo clob from a temp clob.

    CREATE OR REPLACE PROCEDURE ACTIONLOG_APPEND
    (text varchar2, al_id integer) is
    cNew CLOB;
    cOriginal CLOB;
    begin
    update tLOB set var = ' ' || text; -- setting the new text
    select actionmemo into cOriginal
    from actionlog where id = al_id FOR UPDATE; -- getting the original lob
    select var into cNew from tLOB; -- getting the new lob
    DBMS_LOB.APPEND(cOriginal, cNew); -- appending new lob to original lob
    end;

Posting Permissions

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