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 FEBRUARY 21, 2013

Applies to:

Oracle Data Provider for .NET - Version 9.2.0.2.0 and later
Microsoft Windows (32-bit)
***Checked for relevance on 21-Feb-2013***

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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms