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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.