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();
}
...
...
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.