shafeeq c
asked on
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.
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.
ste5an
Can you call/execute this function in SQL Developer without error? Then it is calling issue in your .NET application.
ASKER
I called this function as below in sql developer and i got the response.
var ret varchar2(50);
execute :ret := XF_CALLCENTER.F_CANCELAPPO
print ret;
The expected input type is Number.
Could you please tell me what should be the OracleDbType corresponding to Number
var ret varchar2(50);
execute :ret := XF_CALLCENTER.F_CANCELAPPO
print ret;
The expected input type is Number.
Could you please tell me what should be the OracleDbType corresponding to Number
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
sdstuber came closest to that description early on in the discussion.