How To Resolve Error : ORA-01704: string literal too long

Solve Error : ORA-01704: string literal too long in Oracle quickly. Understand why it happens and learn all proven fixes. Our SQL Server Live Support Team is always here to help you.

How You Can End Error : ORA-01704: string literal too long

You’re trying to insert or update a long text in Oracle, and suddenly, Error: ORA-01704: string literal too long.
This message shows up when Oracle hits its SQL character limit. It’s frustrating, especially when you’re working with XML, JSON, or any large text block.

The main reason is simple: Oracle restricts VARCHAR2 data inside SQL statements to 4000 bytes. Anything beyond that throws this error. Even if your column is declared as CLOB, Oracle still won’t accept a string literal longer than 4000 characters in a SQL command.

Why This Happens

When you insert or update directly with a long string, Oracle treats it as a literal inside SQL. That’s where the limit bites.
You might also run into this when using Data Access Service, because even though the table column is CLOB, the request might still pass data as a VARCHAR type.

As Oracle states, a VARCHAR2 column cannot exceed 4000 bytes, so assigning anything longer instantly causes the ORA-01704: string literal too long error.

How to Resolve the ORA-01704: String Literal Too Long Error

Let’s break down all working methods developers use to solve this, each tested and verified.

1. Set the Right Datatype in Data Access Service

Make sure your data request sends it as a CLOB, not VARCHAR:

<parameterBatch>

<parameter>

<sqlType>clob</sqlType>

<data>{$in.requestObject}</data>

</parameter>

</parameterBatch>

This small change ensures Oracle treats the incoming data correctly.

2. Use Chunked Inserts

Because SQL can only handle up to 4000 bytes in a literal, splitting the text works.

Insert into table (clob_column)

values (to_clob('chunk 1') || to_clob('chunk 2'));

Here, each to_clob() call converts a manageable chunk that Oracle can handle easily.

3. Combine Multiple Chunks Using Variables

For even longer texts, define chunks inside a PL/SQL block:

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;

/

This avoids exceeding the literal limit while keeping the logic clean.

4. Handle Special Characters Safely

Be aware, if your text includes special or multi-byte characters, the total byte count might cross 4000 even faster.
So declaring a variable and using concatenation is still the most reliable route to prevent the ORA-01704: string literal too long issue.

5. Calculate Required Chunks

Before you split, find out how many 4000-byte chunks your string will need:

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

This will show how many complete chunks (and leftover bytes) are needed.
For example:

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

Or, even better, wrap it in a function:

CREATE FUNCTION ret_long_chars RETURN CLOB IS

BEGIN

RETURN to_clob('put here long characters');

END;

Then simply:

UPDATE table SET column = ret_long_chars;

6. Final Trick for SQL Developer

Some users found success with this combination:

DECLARE

str VARCHAR2(32767);

BEGIN

UPDATE table SET column = to_clob('Very-very-...-very long string value');

END;

/

Conclusion

The error isn’t really a bug, it’s a design limit in Oracle SQL.
Once you move long text handling to variables, chunks, or CLOB parameters, this issue disappears for good.

So next time you face Error: ORA-01704: string literal too long, remember, the fix is all about how you pass or build your data, not the data itself.