Working with very large text data and CLOB column

Viewed 81714

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 ?

3 Answers

I have resolve this case with Data Import feature in Oracle SQL Developer:

  1. Make .dsv file with your large string and other attributes.
  2. Just rclick on table and choose 'Data Import'
  3. Choose your file
  4. In Data Import Wizard Step1: select rigth Delimeter, Line Terminator, Row Limit, Encloser characters etc.
  5. Step2: Import Method=Insert, Step3: Map file and table columns to each other
  6. Step4: Run the Data Import
Related