ORA-01704: string literal too long

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.