ORA-01704: string literal too long
Having an Oracle database with a given table storing long strings as binary data types (BLOB/CLOB), one might need to perform manual updates on those columns:
update myTable set lobColumn='Some String Value' where somekey=somecriteria;
The following error might occur while performing the direct update on that column:
ORA-01704: 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.
The origin of the error is that the string value passed is indeed over 4000 characters long. A simple workaround script that works like a charm is:
DECLARE
vString myTable.lobColumn.%type;
BEGIN
vString := 'Some very long string value'
update myTable set lobColumn=vString where somekey=somecriteria;
END;
Don’t forget to replace “myTable” and “lobColumn” with your table and column.
HTH.