The number of parameters does not match number of values for stored procedure

Question

I am using Enterprise Library 5x DAAB to call an Oracle stored procedure with one input parameter and one output parameter. But it gives this error:

The number of parameters does not match number of values for stored procedure

My code is below:

OracleDatabase _database;
OracleConnection _connection;
...
...
DbCommand insertCommand = _database.GetStoredProcCommand("spName");
insertCommand.Connection = _connection;    
_database.AddInParameter(insertCommand, "inParam", DbType.Int16);
_database.AddOutParameter(insertCommand, "outParam", DbType.Byte, 1);
insertCommand.Prepare();

object[] paramValues = new object[] { 1, 2  };
_database.AssignParameters(insertCommand, paramValues);
_database.ExecuteNonQuery(insertCommand, _transaction);
int outParamVal = (int)_database.GetParameterValue(insertCommand, "outParam");

Please note: the above code is a mini sample of the full source. But depicts similar functionality.

Here's the SP I'm using:

create or replace
PROCEDURE SP_APPEND_TO_PRICE_TRANSFER 
(
  FISCAL_YEAR IN NUMBER  
, FISCAL_MONTH IN NUMBER  
, PRODUCT_ID IN NUMBER
, RD_MARKUP IN NUMBER
, RD_RETAIL_PRICE IN NUMBER
, BL_MARKUP IN NUMBER
, BL_RETAIL_PRICE IN NUMBER
, RD_SPEC_FLAG IN VARCHAR2
, BL_SPEC_FLAG IN VARCHAR2
, LEMONADE_DISTRIB_PRICE IN NUMBER
, AFS_DISTRIB_PRICE IN NUMBER
, GLAZIER_DISTRIB_PRICE IN NUMBER
, GLAZIER_DISTRIB_CASE_PRICE IN NUMBER
, SARALEE1_DISTRIB_PRICE IN NUMBER
, SARALEE1_DISTRIB_CASE_PRICE IN NUMBER
, CAROLINA_DISTRIB_PRICE IN NUMBER
, NETUNITCOST IN NUMBER
, RD_INTERNAL_MARKUP IN NUMBER
, BL_INTERNAL_MARKUP IN NUMBER
, RD_TRANSFER_COST IN NUMBER
, BL_TRANSFER_COST IN NUMBER
, TOTAL_NET_WEIGHT IN NUMBER
, PRICE_TRANSFER_UOM IN VARCHAR2
, STD_PACK IN NUMBER
, PAGE_LOCATION_CODE IN VARCHAR2
, PPOD1_DISTRIB_PRICE IN NUMBER
, PPOD1_DISTRIB_CASE_PRICE IN NUMBER
, PPOD1_STOCK_FLAG IN VARCHAR2
, SLASHOUT_PRICE_RD IN NUMBER
, SLASHOUT_PRICE_BL IN NUMBER
, ROWSINSRTD OUT NUMBER
) AS 
BEGIN
  INSERT INTO PRICE_TRANSFER ( 
    FISCAL_YEAR, 
    FISCAL_MONTH, 
    PRODUCT_ID, 
    CHI_REBATE_PCT, 
    CHI_RETAIL_PRICE, 
    NE_REBATE_PCT, 
    NE_RETAIL_PRICE, 
    CHI_SPEC_FLAG, 
    NE_SPEC_FLAG, 
    LEMONADE_DISTRIB_PRICE, 
    AFS_DISTRIB_PRICE, 
    GLAZIER_DISTRIB_PRICE, 
    GLAZIER_DISTRIB_CASE_PRICE, 
    SARALEE1_DISTRIB_PRICE, 
    SARALEE1_DISTRIB_CASE_PRICE, 
    CAROLINA_DISTRIB_PRICE, 
    NETUNITCOST, 
    CHI_INTERNAL_MARKUP, 
    NE_INTERNAL_MARKUP, 
    CHI_TRANSFER_COST, 
    NE_TRANSFER_COST, 
    TOTAL_NET_WEIGHT, 
    PRICE_TRANSFER_UOM, 
    CASE_PACK_FACTOR, 
    PAGE_LOCATION_CODE, 
    PPOD1_DISTRIB_PRICE, 
    PPOD1_DISTRIB_CASE_PRICE, 
    PPOD1_STOCK_FLAG, 
    SLASHOUT_PRICE_RD, 
    SLASHOUT_PRICE_BL )
    VALUES (
    FISCAL_YEAR
, FISCAL_MONTH  
, PRODUCT_ID
, RD_MARKUP
, RD_RETAIL_PRICE
, BL_MARKUP
, BL_RETAIL_PRICE
, RD_SPEC_FLAG
, BL_SPEC_FLAG
, LEMONADE_DISTRIB_PRICE
, AFS_DISTRIB_PRICE
, GLAZIER_DISTRIB_PRICE
, GLAZIER_DISTRIB_CASE_PRICE
, SARALEE1_DISTRIB_PRICE
, SARALEE1_DISTRIB_CASE_PRICE
, CAROLINA_DISTRIB_PRICE
, NETUNITCOST
, RD_INTERNAL_MARKUP
, BL_INTERNAL_MARKUP
, RD_TRANSFER_COST
, BL_TRANSFER_COST
, TOTAL_NET_WEIGHT
, PRICE_TRANSFER_UOM
, STD_PACK
, PAGE_LOCATION_CODE
, PPOD1_DISTRIB_PRICE
, PPOD1_DISTRIB_CASE_PRICE
, PPOD1_STOCK_FLAG
, SLASHOUT_PRICE_RD
, SLASHOUT_PRICE_BL );

ROWSINSRTD := sql%rowcount;
END SP_APPEND_TO_PRICE_TRANSFER;

2011-11-02
So, seems like there's no solution for this at the moment. So for the time being, I got my work done the following way. Pretty basic stuff..

...
...
OracleConnection _connection = _dbConnect.OpenOracleDB();   //custom API to get the Connection object
OracleTransaction transaction = _connection.BeginTransaction();
int affectedRows = 0;
OracleCommand insertCommand = new OracleCommand("MyProc", _connection, transaction);
using (insertCommand)
{
    insertCommand.CommandType = CommandType.StoredProcedure;
    OracleParameter param1 = new OracleParameter("PARAM1", OracleType.Number);
    param1.Direction = ParameterDirection.Input;
    insertCommand.Parameters.Add(param1);
    OracleParameter param2 = new OracleParameter("PARAM2", OracleType.Number);
    param2.Direction = ParameterDirection.Input;
    insertCommand.Parameters.Add(param2);
    OracleParameter outParam = new OracleParameter("OUTPARAM", OracleType.Int32);
    outParam.Size = 1;
    outParam.Direction = ParameterDirection.Output;
    insertCommand.Parameters.Add(outParam);                    

    insertCommand.Prepare();

    foreach (MyObject myObject in myObjects)    //myObjects is a list of objects I want to persist
    {
        param1.Value = myObject.Prop1;
        param2.Value = myObject.Prop2;

        insertCommand.ExecuteNonQuery();
        affectedRows = Convert.ToInt32(outParam.Value);
    }

    transaction.Commit();
}
...
...

Do you need to mark your parameters as output parameters somewhere perhaps? — Chris, Oct 27 '11 at 14:27
@Paolo The SP just has an Insert query in it and it returns the number of affected rows through the OUT param. — Kayes, Oct 28 '11 at 06:49
@Chris The out param is marked as OUT in the parameter declaration in the SP. — Kayes, Oct 28 '11 at 06:50
By the way, I've forgotten to add the Prepare() call in the code. I am actually preparing my command. — Kayes, Oct 28 '11 at 06:51
@Kayes: I meant in the code. the Parameters object probably (I've never used Oracle objects) has a Direction property that can be used to say whether it is is in/out/etc. I was wondering if it might be getting confused by the fact they aren't declared appropriately but it's a long shot since I'd expect a different error message if so. — Chris, Oct 31 '11 at 10:13

score 4 · Answer 1 · answered Oct 30 '11 at 04:37

4

By my count, your stored proc has 30 input parameters and 1 out parameter. If you want it to run, you're going to need to supply all 30 input parameters that your proc requires.

answered Oct 30 '11 at 04:37

Joel C

5,547
1
21
31

score 0 · Answer 2 · answered Oct 27 '11 at 14:46

0

MSDN - OracleDatabase.AssignParameters.

From that, I gather it's because you are passing an array of TWO (2) parameters (paramValues) and have only created ONE (1) InParameter of type DbType.Int16.

//FIRST AND ONLY IN PARAM
_database.AddInParameter(insertCommand, "inParam", DbType.Int16);
...
object[] paramValues = new object[] { 1, 2  }; //2 PARAMETER VALUES

Also, see the Using Statement because anything that is IDisposable should really be in a using statement.

answered Oct 27 '11 at 14:46

myermian

31,823
24
123
215

The second item in the array is for the OUT param. I thought it needs to be set also. I removed it and called AssignParameter() with the array with 1 item.. same exception thrown! – Kayes Oct 28 '11 at 06:53
Well, I guess that wasn't it then... must be something in your SP, it might be helpful to post that then. – myermian Oct 28 '11 at 12:40
Sorry for late reply. I've added the stored proc that I'm calling from DAAB. – Kayes Oct 30 '11 at 04:32

The number of parameters does not match number of values for stored procedure

2 Answers2

Linked