Oracle CLOB can't insert beyond 4000 characters?

Question

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)?

did you find the best way to do this? – TuGordoBello Dec 27 '16 at 19:00 — TuGordoBello, Dec 27 '16 at 19:00

score 21 · Answer 1 · edited May 07 '18 at 16:21

21

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 ')
  );

edited May 07 '18 at 16:21

burkay

1,075
1
10
20

answered Mar 14 '18 at 13:35

gunn

316
2
6

2

This four-year-old question already has an accepted (and also four-year-old) answer. Also for future reference, consider adding more than just code, like an explanation of how your code works. – TheJim01 Mar 14 '18 at 15:52
2

You're wrong and @TheJim01 is correct (and not trolling). All answers require explanation, and I'd say that any answer's explanation is *more* important than the code. – Hovercraft Full Of Eels Mar 15 '18 at 17:20
6

Yes the question got an accepted answer years ago. However, @gunn provides another approach which is easier imho. I also needed what OP asked for and preferred this one. I see commentators' points but couldn't leave this answer as-is, so edited the answer. – burkay May 07 '18 at 15:29
Although the approved comment was applicable, this worked perfectly for me in any case and you saved me some trouble :) – Koshux Dec 21 '22 at 00:14
Oracle is the worst database I've ever worked on, often it has the ability to make complex a very simple task! – Michele Feb 17 '23 at 11:18

score 7 · Accepted Answer · answered Aug 23 '13 at 04:50

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;

Thanks for your example , But How can I insert a xml which is like this , jere I have pasted just a portion, original one is > 4000 bytes Gambardella, Matthew XML Developer''s Guide Computer 44.95 2000-10-01 An in-depth look at creating applications with XML. How to make different chunks .I am having very little knowledge about xml in oracle.Please help. — redsoxlost, May 16 '14 at 18:50

score -3 · Answer 3 · answered Mar 07 '19 at 15:10

-3

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

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

answered Mar 07 '19 at 15:10

masterxilo

2,503
1
30
35

Oracle CLOB can't insert beyond 4000 characters?

3 Answers3

Linked