.NET Oracle Provider: Why will my stored proc not work?

Question

I am using the Oracle .NET Provider and am calling a stored procedure in a package. The message I get back is "Wrong number or types in call". I have ensured that the order in which the parameters are being added are in the correct order and I have gone over the OracleDbType's thoroughly though I suspect that is where my problem is. Here is the code-behind:

//setup intial stuff, connection and command string msg = string.Empty; string oraConnString = ConfigurationManager.ConnectionStrings["OracleServer"].ConnectionString; OracleConnection oraConn = new OracleConnection(oraConnString); OracleCommand oraCmd = new OracleCommand("PK_MOVEMENT.INSERT_REC", oraConn); oraCmd.CommandType = CommandType.StoredProcedure;

    try
    {
        //iterate the array
        //grab 3 items at a time and do db insert, continue until all items are gone. Will always be divisible by 3.
        for (int i = 0; i < theData.Length; i += 3)
        {
            //3 items hardcoded for now
            string millCenter = "0010260510";
            string movementType = "RECEIPT";
            string feedCode = null;
            string userID = "GRIMMETTM";
            string inventoryType = "INGREDIENT"; //set to FINISHED for feed stuff
            string movementDate = theData[i + 0];
            string ingCode = System.Text.RegularExpressions.Regex.Match(theData[i + 1], @"^([0-9]*)").ToString();
            string pounds = theData[i + 2].Replace(",", "");

            //setup parameters
            OracleParameter p1 = new OracleParameter("A_MILL_CENTER", OracleDbType.NVarchar2, 10);
            p1.Direction = ParameterDirection.Input;
            p1.Value = millCenter;
            oraCmd.Parameters.Add(p1);

            OracleParameter p2 = new OracleParameter("A_INGREDIENT_CODE", OracleDbType.NVarchar2, 50);
            p2.Direction = ParameterDirection.Input;
            p2.Value = ingCode;
            oraCmd.Parameters.Add(p2);

            OracleParameter p3 = new OracleParameter("A_FEED_CODE", OracleDbType.NVarchar2, 30);
            p3.Direction = ParameterDirection.Input;
            p3.Value = feedCode;
            oraCmd.Parameters.Add(p3);

            OracleParameter p4 = new OracleParameter("A_MOVEMENT_TYPE", OracleDbType.NVarchar2, 10);
            p4.Direction = ParameterDirection.Input;
            p4.Value = movementType;
            oraCmd.Parameters.Add(p4);

            OracleParameter p5 = new OracleParameter("A_MOVEMENT_DATE", OracleDbType.NVarchar2, 10);
            p5.Direction = ParameterDirection.Input;
            p5.Value = movementDate;
            oraCmd.Parameters.Add(p5);

            OracleParameter p6 = new OracleParameter("A_MOVEMENT_QTY", OracleDbType.Int64, 12);
            p6.Direction = ParameterDirection.Input;
            p6.Value = pounds;
            oraCmd.Parameters.Add(p6);

            OracleParameter p7 = new OracleParameter("INVENTORY_TYPE", OracleDbType.NVarchar2, 10);
            p7.Direction = ParameterDirection.Input;
            p7.Value = inventoryType;
            oraCmd.Parameters.Add(p7);

            OracleParameter p8 = new OracleParameter("A_CREATE_USERID", OracleDbType.NVarchar2, 20);
            p8.Direction = ParameterDirection.Input;
            p8.Value = userID;
            oraCmd.Parameters.Add(p8);

            OracleParameter p9 = new OracleParameter("A_RETURN_VALUE", OracleDbType.Int32, 10);
            p9.Direction = ParameterDirection.Output;
            oraCmd.Parameters.Add(p9);

            //open and execute
            oraConn.Open();
            oraCmd.ExecuteNonQuery();
            oraConn.Close();

        }
    }
    catch (OracleException oraEx)
    {
        msg = "An error has occured in the database: " + oraEx.ToString();
    }
    catch (Exception ex)
    {
        msg = "An error has occured: " + ex.ToString();
    }
    finally
    {
        //close connection
        oraConn.Close();

    }

    return msg;

Answer 1

Could it be that any of the parameter values that you assign is in fact null? I am not too sure about the Oracle case, but for SQL Server, assigning null to a parameter will lead to that parameter not being sent when executing the procedure (which will lead to the procedure complaining about the number of parameters). Instead of null you should pass DBNull.Value:

p1.Value = millCenter ?? DBNull.Value;

Answer 2

I think your problem might be with your 9th parameter. If that is a return value from the stored procedure then the return type should be set to ParameterDirection.ReturnValue.

Hope that helps.

Answer 3

I basically stepped on my own toes here... I was setting the parameters in the loop and then not clearing them out after each iteration. I'm good now.