1

While updating a field that will have more than 4000 chars, I was given a error :

ORA-01704: string literal too long

So, going through few blogs, I got this:

declare vClobVal varchar2(32767) := 'long text' begin update FMS_K_OFFICEWISE_LETTER set FKOL_LETTER_BODY = vClobVal where FKOL_OFFICEWISE_LETTER_ID=240; end;

This worked for me when fired at Toad. Now, I created a stored procedure and compiled as :

CREATE OR REPLACE PROCEDURE FMIS3.UPDATE_LETTER_BODY ( body_text IN FMS_K_OFFICEWISE_LETTER.FKOL_LETTER_BODY%type, condition_id IN FMS_K_OFFICEWISE_LETTER.FKOL_OFFICEWISE_LETTER_ID%type ) IS begin update FMS_K_OFFICEWISE_LETTER set FKOL_LETTER_BODY = body_text end;

which is called as :

 call UPDATE_LETTER_BODY('long string',201);

and this does not work for more than 4000 chars again. Can't I define the size of varchar2 as it gave me error? Any suggestions ?

Improve this question
7
  • Do we have to guess the datatypes of all your columns? Commented Dec 19, 2012 at 13:53
  • FKOL_LETTER_BODY has datatype CLOB Commented Dec 19, 2012 at 13:55
  • How are you passing the value to the procedure? Are you sure it's the procedure that's failing, rather than the way you're calling it? Commented Dec 19, 2012 at 14:15
  • Actually, its fine with string that is less than 4000 chars Commented Dec 19, 2012 at 14:27
  • 2
    The string literal you're passing as an argument is still too long; your call is in an SQL context, not PL/SQL, so it's limited to 4000 rather than 32767 charecters. Commented Dec 19, 2012 at 15:14

1 Answer 1

Reset to default
3

Alex Poole is correct, the call is in an SQL context which can only handle 4000 characters. You could solve this by calling the method in a block like this:

Begin UPDATE_LETTER_BODY('long string',201); End; /

Other options include using another method to call Update_Letter_Body, creating a method to be called repeatedly to add text over 4000 characters, or using multiple input parameters.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.