Search code examples
sqloracleclob

How to update CLOB field in Oracle?

How do I update a clob field with 7000 characters?

tbody -> CLOB

update ttable set tbody = 'sample text'
where tid = 13;

Of course, when I do this, I get an error.It may be a very simple question for experts, but it is a really difficult process for me. I'm working in new oracle sql and I can't get over such a problem.

Solution

  • A string literal can contain up to 4000 chracters (or bytes, whichever is smaller) so you cannot have a string literal with 7000 characters and there is no such thing as a CLOB literal. What you need to do is split the string into several literals and then concatenate string literals (of up to 4000 characters/bytes) to an EMPTY_CLOB():

    update ttable
set tbody = EMPTY_CLOB()
            || '4000 characters (or bytes)'
            || 'next 4000 characters (or bytes)'
            || 'next 4000 characters (or bytes)'
            || 'etc.'
where tid = 13;