How to handle Oracle Stored Proc with ASP.NET and Oracle Data Provider?
- by Matt
I have been struggling with this for quite some time having been accustomed to SQL Server. I have the following code and I have verified that the OracleDbType's are correct and have verified that the actual values being passed to the parameters match. I think my problem may rest with the return value. All it does is give me the row count. I read somewhere that the return parameter must be set at the top. The specific error I am getting says,
PLS-00306: wrong number or types of arguments in call to \u0027INSERT_REC\u0027
ORA-06550: line 1, column 7:\nPL/SQL: Statement ignored
The stored procedure is:
PROCEDURE INSERT_REC
(
A_MILL_CENTER IN GRO_OWNER.MOVEMENT.MILL_CENTER%TYPE, --# VARCHAR2(10)
A_INGREDIENT_CODE IN GRO_OWNER.MOVEMENT.INGREDIENT_CODE%TYPE, --# VARCHAR2(50)
A_FEED_CODE IN GRO_OWNER.MOVEMENT.FEED_CODE%TYPE, --# VARCHAR2(30)
--# A_MOVEMENT_TYPE should be ‘RECEIPT’ for ingredient receipts
A_MOVEMENT_TYPE IN GRO_OWNER.MOVEMENT.MOVEMENT_TYPE%TYPE, --# VARCHAR2(10)
A_MOVEMENT_DATE IN VARCHAR2, --# VARCHAR2(10)
A_MOVEMENT_QTY IN GRO_OWNER.MOVEMENT.MOVEMENT_QTY%TYPE, --# NUMBER(12,4)
--# A_INVENTORY_TYPE should be ‘INGREDIENT’ or ‘FINISHED’
A_INVENTORY_TYPE IN GRO_OWNER.MOVEMENT.INVENTORY_TYPE%TYPE, --# VARCHAR2(10)
A_CREATE_USERID IN GRO_OWNER.MOVEMENT.CREATE_USERID%TYPE, --# VARCHAR2(20)
A_RETURN_VALUE OUT NUMBER --# NUMBER(10,0)
);
My code is as follows:
//3 items hardcoded for now
string millCenter = "0010260510";
string movementType = "RECEIPT";
string feedCode = "test this";
string userID = "GRIMMETTM";
string inventoryType = "INGREDIENT"; //set to FINISHED for feed stuff
string movementDate = theData[i];
string ingCode = System.Text.RegularExpressions.Regex.Match(theData[i + 1], @"^([0-9]*)").ToString();
//int pounds = Convert.ToInt32(theData[i + 2].Replace(",", ""));
int pounds = 100;
//setup parameters
OracleParameter p9 = new OracleParameter("A_RETURN_VALUE", OracleDbType.Int32, 30);
p9.Direction = ParameterDirection.ReturnValue;
oraCmd.Parameters.Add(p9);
OracleParameter p1 = new OracleParameter("A_MILL_CENTER", OracleDbType.Varchar2, 10);
p1.Direction = ParameterDirection.Input;
p1.Value = millCenter;
oraCmd.Parameters.Add(p1);
OracleParameter p2 = new OracleParameter("A_INGREDIENT_CODE", OracleDbType.Varchar2, 50);
p2.Direction = ParameterDirection.Input;
p2.Value = ingCode;
oraCmd.Parameters.Add(p2);
OracleParameter p3 = new OracleParameter("A_FEED_CODE", OracleDbType.Varchar2, 30);
p3.Direction = ParameterDirection.Input;
p3.Value = feedCode;
oraCmd.Parameters.Add(p3);
OracleParameter p4 = new OracleParameter("A_MOVEMENT_TYPE", OracleDbType.Varchar2, 10);
p4.Direction = ParameterDirection.Input;
p4.Value = movementType;
oraCmd.Parameters.Add(p4);
OracleParameter p5 = new OracleParameter("A_MOVEMENT_DATE", OracleDbType.Varchar2, 10);
p5.Direction = ParameterDirection.Input;
p5.Value = movementDate;
oraCmd.Parameters.Add(p5);
OracleParameter p6 = new OracleParameter("A_MOVEMENT_QTY", OracleDbType.Int32, 12);
p6.Direction = ParameterDirection.Input;
p6.Value = pounds;
oraCmd.Parameters.Add(p6);
OracleParameter p7 = new OracleParameter("A_INVENTORY_TYPE", OracleDbType.Varchar2, 10);
p7.Direction = ParameterDirection.Input;
p7.Value = inventoryType;
oraCmd.Parameters.Add(p7);
OracleParameter p8 = new OracleParameter("A_CREATE_USERID", OracleDbType.Varchar2, 20);
p8.Direction = ParameterDirection.Input;
p8.Value = userID;
oraCmd.Parameters.Add(p8);
//open and execute
oraConn.Open();
oraCmd.ExecuteNonQuery();
oraConn.Close();
