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

Posted by Matt on Stack Overflow See other posts from Stack Overflow or by Matt
Published on 2010-03-23T16:32:46Z Indexed on 2010/03/30 16:23 UTC
Read the original article Hit count: 347

Filed under:
|
|

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;

© Stack Overflow or respective owner

Related posts about .NET

Related posts about oracle10g