What is the cause of the ORA-01704: string literal too long ?
or
How to insert records length larger than 4000 into CLOB columns?
Cause:
The string literal is longer than 4000 characters.
Action:
Use a string literal of at most 4000 characters.
Longer values may only be entered using bind variables.
Below are the required steps in order to regenerate the use case:-
1. Create a dummy table:-
create table test_clob (mtype varchar2(20),dummy_clob clob);
2. Try to insert a text having length greater than 4000.
insert into test_clob values ('try1','This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text;');
Error:- ORA-01704: string literal too long
Solution:-
Following are the two solutions:-
1.In SQL there is a limit of 4000 character to a varchar2.
And anything enclosed in single quotes is by default, treated as a varchar2.
Break your text into 4000 character blocks and enclose each chunk with function TO_CLOB(...), and concatenate these clobs.
Below is the example for the same.
insert into test_clob values ('Solution1',
to_clob('This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; ') ||
to_clob(' This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text;'));
2. By using PL/SQL, assign the large text to a clob variable & use this variable to insert the data.
DECLARE
large_txt CLOB;
BEGIN
large_txt:='This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text; This is a very large text;';
insert into test_clob
values ('Solution2',
large_txt);
END;
Hope this article will help you out in order to solve the error
ORA-01704: string literal too long.
