Ozzie.eu

Love to code, although it bugs me.

ORA-01704: string literal too long

Ozzie 11:52 AM 2 comments
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.

2 comments :

  1. Vinod MadyalkarNovember 19, 2012 at 5:02 PM

    I tried doing the same but its not working... My file is around 32000 characters long... I created a .sql file and ran the same in sqlplus.. It is not throwing any error, but its showing numbers 999,1000,1001 everytime I press the "enter" key.. My file has 995 lines... Can you please tell me what might be the cause??.. thanks..

    ReplyDelete
  2. OzzieNovember 19, 2012 at 5:37 PM

    Sounds like you haven't ended your instruction block on your pl/sql script. The numbers are sql*plus way of saying you're adding empty lines to the script. Did you put a semi-colon on your last statement? HTH.

    ReplyDelete

Subscribe to: Post Comments ( Atom )