Error : ORA-01704: string literal too long

While I try to set the value of over 4000 characters on a field that has data type CLOB, it gives me this error :

ORA-01704: string literal too long.

Any suggestion, which data type would be applicable for me if I have to set value of unlimited characters although for my case, it happens to be of about 15000 chars.

Note : The long string that I am trying to store is encoded in ANSI.

7 Answers

What are you using when operate with CLOB?

In all events you can do it with PL/SQL

DECLARE str varchar2(32767); BEGIN str := 'Very-very-...-very-very-very-very-very-very long string value'; update t1 set col1 = str; END; / 

Proof link on SQLFiddle

Try to split the characters into multiple chunks like the query below and try:

Insert into table (clob_column) values ( to_clob( 'chunk 1' ) || to_clob( 'chunk 2' ) ); 

It worked for me.

To solve this issue on my side, I had to use a combo of what was already proposed there

DECLARE chunk1 CLOB; chunk2 CLOB; chunk3 CLOB; BEGIN chunk1 := 'very long literal part 1'; chunk2 := 'very long literal part 2'; chunk3 := 'very long literal part 3'; INSERT INTO table (MY_CLOB) SELECT ( chunk1 || chunk2 || chunk3 ) FROM dual; END; 

Hope this helps.

The split work until 4000 chars depending on the characters that you are inserting. If you are inserting special characters it can fail. The only secure way is to declare a variable.

Though its a very old question but i think sharing experience still might help others:

Large text can be saved in a single query if we break-down it in chunks of 4000 bytes/characters by concatinating them using '||'

Running following query will tell you:

1. Required Number of chunks containing 4000 bytes
2. Remaining bytes

Since, in given example you are trying to save text contining 15000 bytes (characters), so,

select 15000/4000 chunk,mod(15000,4000) remaining_bytes from dual; 

Result:

That means, you need to concatenate 3 chunks of 4000 bytes and one chunk of 3000 bytes, so it would be like:

INSERT INTO <YOUR_TABLE> VALUES (TO_CLOB('<1st_4K_bytes>') || TO_CLOB('<2nd_4K_bytes>') || TO_CLOB('<3rd_4K_bytes>') || TO_CLOB('<last_3K_bytes>)'); 

INSERT INTO table(clob_column) SELECT TO_CLOB(q'[chunk1]') || TO_CLOB(q'[chunk2]') || TO_CLOB(q'[chunk3]') || TO_CLOB(q'[chunk4]') FROM DUAL; 

Accepted answer did not work for me in sql developper but combination of this answer and another one did :

DECLARE str varchar2(32767); BEGIN update table set column = to_clob('Very-very-...-very-very-very-very-very-very long string value') /