More than 4000 chars gives string literal too long error on oracle

Question

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 ?

Answer 1

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.