6

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 ?

Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236
  • 1
    Where does this data come from? Typing 100k characters would be a poor choice so straight SQL or anonymous PL/SQL block is not a good idea. If the data is on files, you can use `UTL_FILE` directly. If the data comes from an application, you can use whatever language it is written to interact with the clob objects direcly. – Vincent Malgrat Dec 16 '13 at 14:42
  • Thanks for answer, I am just need to store large text into clob column – Oto Shavadze Dec 16 '13 at 19:15

3 Answers3

7

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;
Alen Oblak
  • 3,285
  • 13
  • 27
  • Thanks, I am trying with `dbms_lob.append`, though also got error: `string literal too long`. Please see update in question. – Oto Shavadze Dec 17 '13 at 06:56
  • Varchar is limited to 32k characters. Your `c2` variable can't hold 100k characters. You must call the function `append` multiple times, every time adding at most 32k characters. – Alen Oblak Dec 17 '13 at 07:29
  • A technical note. If you omit the assignment c1 := 'abc', and if you change the declaration with c1 clob := EMPTY_CLOB() you obtain the INVALID LOB LOCATOR error. The clob has always to be assigned with a non empty value. – UltraCommit Dec 11 '18 at 13:52
  • How can we dynamically call append function multiple times till the string chucks end? – Pratik Rawlekar May 08 '19 at 10:21
2

I found this question while Googling how to append data to a CLOB. For my particular problem, I'm using a legacy PL/SQL system where I can't make use of the dbms_lob package, so I thought that I would share my answer for the benefit of others in my situation.

Solution: Use Oracle's CONCAT function in aSELECTquery, theCONCATfunction works for theCLOB` data type. For example (using @AlenOblak's example):

declare
  c1 clob;
  c2 varchar2(32000);
begin
  c1 := 'abc';
  c2 := 'text, which contains 32 000 characters';
  SELECT CONCAT(c1, c2) INTO c1 FROM DUAL;
  c2 := 'some more text, which contains 32 000 characters';
  SELECT CONCAT(c1, c2) INTO c1 FROM DUAL;
  insert into t1 values (c1);
end;

Hope that helps.

entpnerd
  • 10,049
  • 8
  • 47
  • 68
0

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
rustohero
  • 38
  • 7