Oracle managed data access: throws not enough values c#

Question

Though I found many answers to the same question but non of those solved my problem, so I'm posting this question. I am getting not enough values exception while trying to insert data to Oracle DB using Oracle.ManagedDataAccess in c#.

Below my code

string cmdQuery = string.Format("INSERT INTO HKSTF087.FREIGHTCHARGES" +
"(QTM_ID,TITLECODE,SERVICETYPE,FREIGHTCHARGERATE,FREIGHTCHARGETYPE,FREIGHTCHARGETARRIFCODE," +
"ORIGINID,FREIGHTCALCULATIONTYPE,EFFECTIVEFROMDATE,EFFECTIVETHRUDATE,CREATEDBY,CREATEDDATETIME,UPDATEDBY,UPDATEDDATETIME)" +
"VALUES(:QTM_ID,:TITLECODE,:SERVICETYPE,:FREIGHTCHARGERATE,:FREIGHTCHARGETYPE,:FREIGHTCHARGETARRIFCODE" +
":ORIGINID,:FREIGHTCALCULATIONTYPE,:EFFECTIVEFROMDATE,:EFFECTIVETHRUDATE,:CREATEDBY,:CREATEDDATETIME,:UPDATEDBY,:UPDATEDDATETIME)");

OracleParameter[] parameters = new OracleParameter[]
{
    new OracleParameter("QTM_ID",OracleDbType.Int32,origin.OriginId, ParameterDirection.Input),
    new OracleParameter("TITLECODE", OracleDbType.Varchar2, origin.TitleCode.Trim(), ParameterDirection.Input ),
    new OracleParameter("SERVICETYPE", OracleDbType.Varchar2, origin.ServiceType.Trim(), ParameterDirection.Input ),
    new OracleParameter("FREIGHTCHARGERATE", OracleDbType.Decimal, origin.FreightChargeRate, ParameterDirection.Input ),
    new OracleParameter("FREIGHTCHARGETYPE", OracleDbType.Varchar2, origin.FreightChargeRateType, ParameterDirection.Input ),
    new OracleParameter("FREIGHTCHARGETARRIFCODE", OracleDbType.Varchar2, origin.FreightChargeTariffCode, ParameterDirection.Input ),
    new OracleParameter("ORIGINID", OracleDbType.Varchar2, origin.OriginId.ToString(), ParameterDirection.Input ),
    new OracleParameter("FREIGHTCALCULATIONTYPE", OracleDbType.Varchar2, origin.FreightCalculationType, ParameterDirection.Input ),
    new OracleParameter("EFFECTIVEFROMDATE", OracleDbType.Date, effectiveFromDate, ParameterDirection.Input ),
    new OracleParameter("EFFECTIVETHRUDATE", OracleDbType.Date, "12/31/2099", ParameterDirection.Input ),
    new OracleParameter("CREATEDBY", OracleDbType.Varchar2, "system", ParameterDirection.Input ),
    new OracleParameter("CREATEDDATETIME", OracleDbType.Date, createdDate, ParameterDirection.Input ),
    new OracleParameter("UPDATEDBY", OracleDbType.Varchar2, null, ParameterDirection.Input ),
    new OracleParameter("UPDATEDDATETIME", OracleDbType.Date, null, ParameterDirection.Input )
};
//other code 
cmd.ExecuteNonQuery();

cmd.ExecuteNonQuery(); throws exception ORA-00947: not enough values but As far I can see all parameter are just fine and perhaps I'm not able to see the cause of the problem. I also have done similar thing with another table and that does inserts data to the table.

to11mtm · Accepted Answer · 2017-10-21 21:41:27Z

2

"VALUES(:QTM_ID,:TITLECODE,:SERVICETYPE,:FREIGHTCHARGERATE,:FREIGHTCHARGETYPE,:FREIGHTCHARGETARRIFCODE" +
":ORIGINID,:FREIGHTCALCULATIONTYPE,:EFFECTIVEFROMDATE,:EFFECTIVETHRUDATE,:CREATEDBY,:CREATEDDATETIME,:UPDATEDBY,:UPDATEDDATETIME)");

I'm noticing the lack of a comma after 'FREIGHTCHARGETARRIFCODE', that might do it on oracle.

Also, it's been a little while since I've used ODP, but I think the use of null instead of DbNull.Value could be tripping it up and trimming those parameters instead of including them. (Edit: Also the tossing of a string for the oracle date may or may not be an issue.)

All of this is also assuming that you're adding the parameters to the command in the elided code.

answered Oct 21, 2017 at 21:41

to11mtm

1689 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle managed data access: throws not enough values c#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related