Oracle Function Call

I have a function defined in Oracle with Number as input.
I am using vb.net to call that function. The output is Varchar2 value.
But when i run the code, i am getting the error "ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1" .

Actually it's a function to cancel the appointments. The appointment is getting cancelled in database, but the ExecuteNonQuery always returns me this error.
shafeeq cAsked:
Who is Participating?
 
sdstuberCommented:
the problem is likely that your function is returning a value greater than 50 characters long

or, inside the function some operation assigns a string value to a variable that won't fit.

what do you get if you execute this query?

select XF_CALLCENTER.F_CANCELAPPOINTMENT(11804383) from dual
0
 
ste5anSenior DeveloperCommented:
Can you call/execute this function in SQL Developer without error? Then it is calling issue in your .NET application.
0
 
shafeeq cAuthor Commented:
I called this function as below in sql developer and i got the response.

var ret varchar2(50);
execute :ret := XF_CALLCENTER.F_CANCELAPPOINTMENT(11804383);
print ret;

The expected input type is Number.
Could you please tell me what should be the OracleDbType corresponding to Number
0
 
jtriftsMI and AutomationCommented:
plsql numeric or value error is pretty clear. The return value is either the wrong size, or the wrong type.
Seems to me that your output is not a VARCHAR2, or, more likely, it is returning a value which is greater than 50 characters.

I would try looking at the definition of the function you are calling. Is it possible that the output is a data type which cannot be implicitly converted to VARCHAR2? (for example, if it was a record type,

If, for example, the return can be bigger than 50 characters, that too would also cause a 6502 error to be raised since your variable, capturing the return code only holds a max of 50 characters.
0
 
jtriftsMI and AutomationCommented:
6502 errors are usually caused by trying to stuff too much data into a field/column, or alternatively trying to insert into a type which causes an implicit conversion which cannot succeed (e.g. inserting a varchar2 data type into a numeric).

sdstuber came closest to that description early on in the discussion.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.