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

Answer 1

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