ORA-06502: PL/SQL: numeric or value error calling a stored procedure from c# web service

Question

I'm trying to write a web service that calls an oracle stored procedure. The procedure has 2 inputs - a string (company_code) and an int (customer code). It has 2 output parameters; a number (pout_addr_code) and a varchar2 (pout_descr). When I run this procedure from SQL Developer everything works fine and both values are returned as expected. When I call the procedure from my C# web service I get the error "Oracle.DataAccess.Client.OracleException: ORA-06502: PL/SQL: numeric or value error" on execution, not during the compile.

if I comment out the cmd.ExecuteNonQuery() and the return pout_addr_code.Value... lines it will run fine and output my test string.

If I remove the varchar2 output from the procedure and comment out the section adding the pout_descr parameter and commenting out the return "I returned..." it runs fine, including returning the value from the stored procedure.

So it seems to me the problem is in executing the NonQuery when the procedure has the OracleDbType.Varchar2 line. Is there some other way that this is defined when adding the output parameter in C# using the Oracle.DataAccess reference? I found this post asking about a similar error but his solution was to 'specify the max of VARCHAR2 to 32767 in C#' but I don't see how you do that.

here's my c# code:

public string AutoPmtPost(string company_code, int customer_code)
    {
        string oradb = "Data Source=MySource; User Id=MyID; Password=MyPassword;";
        OracleConnection conn = new OracleConnection(oradb);
        conn.Open();
        OracleCommand cmd = new OracleCommand("USP_JEFF_TEST", conn);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("pin_company_code", OracleDbType.Varchar2, ParameterDirection.Input).Value = company_code;

        cmd.Parameters.Add("pin_customer_code", OracleDbType.Int32, ParameterDirection.Input).Value = customer_code;

        OracleParameter pout_addr_code = new OracleParameter("pout_addr_code", OracleDbType.Int32);
        pout_addr_code.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(pout_addr_code);

        OracleParameter pout_descr = new OracleParameter("pout_descr", OracleDbType.Varchar2);
        pout_descr.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(pout_descr);

        cmd.ExecuteNonQuery();
        //return pout_addr_code.Value.ToString();
        return "I returned something";
    }

Thanks.

Answer 1

OracleParameter pout_descr = new OracleParameter("pout_descr", OracleDbType.Varchar2);

should be

OracleParameter pout_descr = new OracleParameter("pout_descr", OracleDbType.Varchar2, 2000);