.NET Oracle Provider: Why will my stored proc not work?
- by Matt
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;
