How to update CLOB field in Oracle?-CodePudding

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.

CodePudding user response：

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;

CodePudding user response：

Alternative solution. Here, it is necessary to split the text so that it does not exceed 4000 characters as in MT0's solution.

DECLARE
  vc_body CLOB := 'bla bla bla ...' || 'bla bla bla ...';
BEGIN
  UPDATE ttable SET tbody =  vc_body WHERE tid= 13;
  commit;
END;