How to handle Oracle Stored Proc with ASP.NET and Oracle Data Provider?

Question

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:

 for (int i=0; i < theData.Length -1; i+=3)
                {
//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();

Answer 1

Have you tried adding the RETURN_VALUE parameter last instead of first? If I recall, parameters have to be in the exact same order when calling an Oracle stored procedure through the Oracle provider.

Answer 2

ParameterDirection for A_RETURN_VALUE should be ParameterDirection.Output

Answer 3

Can't tell from your sample, but could it be that you are declaring the oraCmd object outside the scope of the for loop? I don't see the declaration in the example, but if this is the case, then each iteration would add all the Oracle Parameters to the array and you would have too many on any runs after the first.