Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
Do we have to guess the datatypes of all your columns? –  Vincent Malgrat Dec 19 '12 at 13:53
    
FKOL_LETTER_BODY has datatype CLOB –  hsuk Dec 19 '12 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? –  Alex Poole Dec 19 '12 at 14:15
    
Actually, its fine with string that is less than 4000 chars –  hsuk Dec 19 '12 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. –  Alex Poole Dec 19 '12 at 15:14
show 2 more comments

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.