Search notes:

ORA-01704: string literal too long

The following SQL statement selects a string whose length is exactly 4000 characters. If only one character were added, Oracle would throw a ORA-01704: string literal too long error because the maximum length of string literals in SQL statements is 4000.

select
length('xxxx      A line with exactly one hundred characters     xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line  2                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line  3                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line  4                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line  5                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line  6                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line  7                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line  8                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line  9                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 10                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 11                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 12                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 13                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 14                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 15                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 16                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 17                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 18                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 19                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 20                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 21                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 22                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 23                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 24                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 25                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 26                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 27                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 28                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 29                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 30                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 31                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 32                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 33                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 34                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 35                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 36                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 37                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 38                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 39                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
This is line 40                                          xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') length
from
  dual;

A workaround for such errors is to create a CLOBs by concatenating CLOBs whose length is less then 4000 characters:

select
   to_clob('string with less than 4000') ||
   to_clob('string with less than 4000') …
from
  dual;

If long strings are needed for DML statements, a workaround is to use an anonymous PL/SQL block:

declare
   txt clob := q'[ imagine long text here ]';
begin
   update tab set xyz = txt where id = 42;
end;
/

ORA-01704: string literal too long

See also