Oracle CLOB can't insert beyond 4000 character?

oracle insert clob
Dolphin picture Dolphin · Aug 23, 2013 · Viewed 31.9k times · Source

How to insert more than 4000 characters to 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.

When I want to insert string >4000 for one time, how to do it? Is it be possible?

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

Answer

gunn picture gunn · Mar 14, 2018
  • 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 ')
  );

Related questions

Java: How to insert CLOB into oracle database

I need to write an XML file content into oracle database where the column is of CLOB datatype. How will I do that?

Read More
Insert CLOB into Oracle database

My question is: How do you get around the ORA-01704: string literal too long error when inserting (or doing anything in queries) with CLOBs? I want to have a query like this: INSERT ALL INTO mytable VALUES ('clob1') INTO …

Read More
Oracle SQL: Use sequence in insert with Select Statement

Basically I want to run the following query: INSERT INTO historical_car_stats (historical_car_stats_id, year, month, make, model, region, avg_msrp, count) SELECT my_seq.nextval, '2010', '12', 'ALL', 'ALL', region, sum(avg_msrp * count) / …

Read More