CODEWITHSUNDEEP
oracle-databaseinsertclob
Dolphin
Dolphin

Reputation: 38607

Oracle CLOB can't insert beyond 4000 characters?

How do I insert more than 4000 characters to a CLOB type column?

--create test table s
create table s
(
      a clob
);
insert into s values('>4000 char')

Results in an error:

ORA-01704:the string too long.

I want to insert a string of >4000 characters one time. How do I do it? Is it possible?

When I read the Oracle reference, CLOB can save max 4GB(Gigabyte)?

Upvotes: 20

Views: 64991

Answers (3)

masterxilo
masterxilo

Reputation: 2778

Use a clob column and use sqlldr to import the data from a csv.

sqldeveloper can generate the necessary control .ctl script for you.

Upvotes: -3

gunn
gunn

Reputation: 321

  • split the long character string into 4000 character or less chunks
  • create clobs for each chunk using to_clob() function
  • concatenate the clobs

Here is an example:

insert into <table> (clob_column)
  values
  (
      to_clob(' <=4000 symbols ')
    ||to_clob(' <=4000 symbols ')
    ||to_clob(' <=4000 symbols ')
    ...
    ||to_clob(' <=4000 symbols ')
  );

Upvotes: 22

Ra&#250;l Ju&#225;rez
Ra&#250;l Ju&#225;rez

Reputation: 2159

The maximum for one time insertion is 4000 characters (the maximum string literal in Oracle). However you can use the lob function dbms_lob.append() to append chunks of (maximum) 4000 characters to the clob:

CREATE TABLE don (x clob);


DECLARE 
 l_clob clob;
BEGIN
  FOR i IN 1..10
  LOOP
    INSERT INTO don (x) VALUES (empty_clob()) --Insert an "empty clob" (not insert null)
    RETURNING x INTO l_clob;

    -- Now we can append content to clob (create a 400,000 bytes clob)
    FOR i IN 1..100
    LOOP
      dbms_lob.append(l_clob, rpad ('*',4000,'*'));
      --dbms_lob.append(l_clob, 'string chunk to be inserted (maximum 4000 characters at a time)');
    END LOOP;
  END LOOP;
END;

Upvotes: 8

Related Questions