Love to code although it bugs me.

03 Nov 2022

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:

  vString myTable.lobColumn.%type;
  vString := 'Some very long string value'
  update myTable set lobColumn=vString where somekey=somecriteria;

Don’t forget to replace “myTable” and “lobColumn” with your table and column.