Working with very large text data and CLOB column

lob, oracle, oracle11g

Solution

You should use the `dbms_lob` package, the procedure to add some string to the clob is `dbms_lob.append`.

DBMS_LOB documentation

declare
  c1 clob;
  c2 varchar2(32000);
begin
  c1 := 'abc';
  c2 := 'text, which contains 32 000 characters';
  dbms_lob.append(c1, c2);
  c2 := 'some more text, which contains 32 000 characters';
  dbms_lob.append(c1, c2);
  insert into t1 values (c1);
end;

Problem

According to documentation CLOB and NCLOB datatype columns, can store up to 8 terabytes of character data. I have text, which contains 100 000 character, how can I run query like this: ``` UPDATE my_table SET clob_column = 'text, which contains 100 000 characters' WHERE id = 1 ``` ? If in text, character count is up to 32767, there is possible to use PL/SQL anonymous block: ``` DECLARE myvar VARCHAR2(15000); BEGIN myvar := 'text, which contains 100 000 characters'; UPDATE my_table SET clob_column = myvar WHERE id = 1; .... END; ``` What is solution, where text is very large and contains for example 100 000 characters ? update I am trying with `dbms_lob.append`: ``` create table t1 (c clob); declare c1 clob; c2 clob; begin c1 := 'abc'; c2 := 'text, which contains 100 000 characters'; dbms_lob.append(c1, c2); insert into t1 values (c1); end; ``` Though, also got error: `string literal too long`. I am doing something wrong ?

Original source