Oracle Stored Procedure Help!!!!!!!!!!!!!!!

[dsudhir]

Hi,

I am getting this error when i run a stored procedure from my asp page.

Error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "default.asp", line 107 ORA-06512: at line 1

in my stored procedure i have a variable 'sname' to which i concat string. if the string length exceeds a particular length then i get this error else it the procedure work fine

example

ssubject := 'This is what error i am getting when i run'
sname := 'hi how are u doing here' || ssubject

if i change the lenght of any one variable then it works fine
example
ssubject := 'This is what error'
sname := 'hi how are u doing here' || ssubject

Can anyone help me with this.
I would really appreciate it.

Thanks
sudhir

 

[karluk]

It certainly appears that sname is defined to be too small to hold the larger strings which you are attempting to assign to it. You will probably have to modify the stored procedure to increase the maximum length - either that or put an edit in your application to prevent calling the stored procedure with too long an input.

 

[carp]

Yes, that sounds right. Recommend you change sname to be a VARCHAR2(4000) and try that. If that fixes the problem, then you can start whittling this value down from 4000 if you want to.