ORA-06502 Calling a PL/SQL Function That Passes a VARCHAR2 IN Parameter and Returns a VARCHAR2 RETURN Parameter
(Doc ID 261084.1)
Last updated on MARCH 14, 2025
Applies to:
Oracle Data Provider for .NET - Version 9.2.0.2.0 and laterMicrosoft Windows (32-bit)
Symptoms
When attempting to call an oracle PL/SQL function using c# and ODP.Net that passes an IN parameter and returns a value, an OracleException occurs reporting an ORA-06502 PL/SQL numeric or value error along with the ORA-06512 reporting a line number for the PL/SQL code.
This occurs when your code sets the IN parameter prior to the RETURN parameter as follows:
OracleConnection con = new OracleConnection("Data Source=ORCL92;User ID=test;Password = test");
OracleCommand cmd = new OracleCommand("test_function", con);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter test_input = new OracleParameter("test_input", OracleDbType.Varchar2);
test_input.Direction = ParameterDirection.Input;
test_input.Value = "blah";
cmd.Parameters.Add(test_input);
OracleParameter test_return = new OracleParameter("test_return", OracleDbType.Varchar2,60);
test_return.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(test_return);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Console.WriteLine("{0}",test_return.Value);
Console.WriteLine("Press Enter to Continue");
Console.ReadLine();
}
catch(OracleException e)
{
Console.WriteLine(e.Number);
Console.WriteLine(e.Message);
Console.WriteLine("test failed\nPress Enter to Continue");
Console.ReadLine();
} Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |