ORA-01704: string literal too long

ORA-01704: string literal too long error occurs when a string literal with more than 32767 characters is used in an Oracle SQL query. Oracle supports string literals with a maximum length of 32767 characters or 4000 bytes. If a string with more than the maximum number of characters is used, Oracle will throw the error ORA-01704: string literal too long. If you need to use a string with more than 32767 characters, use the CLOB (Character Large Object) data type, which can hold up to 2,147,483,647 characters.

If a string is created with a length greater than the maximum allowed limit by Oracle, the string literal cannot be created. In Oracle, the maximum length for a string literal is 32767 characters or 4000 bytes. Oracle will throw an error if the string exceeds the maximum length. The string literal should be truncated within the oracle’s maximum limit. Otherwise, the string datatype should be changed to a CLOB data type that can store more than the string literals’ maximum limit.



The Problem

The ORA-01704: string literal too long error will be thrown if a string with more than 32767 characters is used in the Oracle SQL query. The string could be sent from the application or from a CLOB column. Oracle will throw this error if the application sends string data that is longer than 32767 characters. Oracle will throw this error if you convert the CLOB datatype column data to a string.

select length('A string literal with length more than 32767 characters') from dual;

Error

ORA-01704: string literal too long
01704. 00000 -  "string literal too long"
*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.
Error at Line: 1 Column: 15


Solution 1

If you use a string literal that is longer than 32767 characters, split it into two or more smaller strings that are less than the maximum length. Oracle will process the string if it is less than the maximum allowed limit. The issue will be fixed.

Error

select length('A string literal with length more than 32767 characters') from dual;

Solution

select length('Changed the string literal with length less than OR equals to 32767 characters') from dual;

Output

LENGTH('ABC....')
-----------------
32767


Solution 2

If you want to use a string literal with a length that exceeds the maximum allowed limit, split the string into multiple strings and convert to the CLOB datatype first. Add the CLOB strings together to form a single CLOB string that contains the entire string.

Error

select length('A string literal with length more than 32767 characters') from dual;

Solution

select length(
	to_clob('String literal with length less than 32767 characters') ||
	to_clob('String literal with length less than 32767 characters')
) from dual;

Output

LENGTH(TO_CLOB('ABC....')
-----------------
32780


Solution 3

If you want to use the longer string contains length more than maximum allowed limit from the CLOB table column, you can use the SUBSTR function to split the whole CLOB string to oracle string and use it in the sql query. The applications such as java, dot net etc supports to store the CLOB table data in the application layer.

Solution

CREATE TABLE test
(
     name VARCHAR2(90),
     htmlcode CLOB
);

insert into test values ('sample data', 
	to_clob('String literal with length less than 32767 characters') ||
	to_clob('String literal with length less than 32767 characters')
);

select substr(htmlcode,0,32767) from test;

Output

SUBSTR(HTMLCODE,0,32767)
-----------------
STRING WITH LESS THAN 32767 CHARACTERS



Leave a Reply