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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s