ORA-00932: inconsistent datatypes: expected NUMBER got DATE

Question

I don't know why I am getting this exception. Parameter orders are same. No problem with datatype. I have also tried this: TO_DATE(:UPDATE_DATE, 'MM/DD/YYYY HH24:MI:SS') nothing has changed. My insert command runs successfully with date field what is the problem in this update command? When I remove date field I was able to update successfully so I am sure that there is a problem with UPDATE_DATE column. Any suggestions?

var commandTxt = @"UPDATE CFG_PERMISSION 
                                SET CATEGORY= :CATEGORY,                                    
                                    TYPE_CODE = :TYPE_CODE, 
                                    LOCATION_ID = :LOCATION_ID, 
                                    OBJECT_ID = :OBJECT_ID, 
                                    NAME = :NAME, 
                                    DESCRIPTION = :DESCRIPTION,  
                                    UPDATE_DATE = :UPDATE_DATE,     
                                    UPDATED_BY = :UPDATED_BY                                        
                                WHERE ID = :SCOPE_ID";


            OracleParameter[] parameters = new OracleParameter[] {
                new OracleParameter(":SCOPE_ID", OracleDbType.Long,10, cfgRoleScope.ID, ParameterDirection.Input),
                new OracleParameter(":CATEGORY",OracleDbType.Varchar2,30, cfgRoleScope.CATEGORY, ParameterDirection.Input),
                new OracleParameter(":TYPE_CODE", OracleDbType.Varchar2,30, cfgRoleScope.TYPE_CODE,ParameterDirection.Input),
                new OracleParameter(":LOCATION_ID",OracleDbType.Long,10, DBNull.Value, ParameterDirection.Input),
                new OracleParameter(":OBJECT_ID", OracleDbType.Long,10, DBNull.Value, ParameterDirection.Input),
                new OracleParameter(":NAME", OracleDbType.Varchar2,50, cfgRoleScope.NAME,ParameterDirection.Input),
                new OracleParameter(":DESCRIPTION", OracleDbType.Varchar2,50, cfgRoleScope.DESCRIPTION,ParameterDirection.Input),
                new OracleParameter(":UPDATE_DATE",OracleDbType.Date,DateTime.Now,ParameterDirection.Input),
                new OracleParameter(":UPDATED_BY",OracleDbType.Long,6,cfgRoleScope.UPDATED_BY, ParameterDirection.Input),                    

            };

            if (cfgRoleScope.OBJECT_ID > 0)
                parameters[4].Value = cfgRoleScope.OBJECT_ID;

            if (cfgRoleScope.LOCATION != null)
                parameters[3].Value = cfgRoleScope.LOCATION.ID;

            var command = db.CreateCommand();
            command.Parameters.AddRange(parameters.ToArray());
            command.CommandText = commandTxt;
            command.ExecuteNonQuery();

You actually dont need parameter for date instead use sysdate as you need current date time. Also see the format of DateTime.Now and check what format you are accepting in DB — LifeOfPi
@Prathyush I know I can use SYSDATE but I wanted to learn why couldn't I pass that date parameter. Finally, found out that scope_id should be the last parameter. that is why it gives this error. — cihadakt

cihadakt cihadakt · Accepted Answer · 2016-07-21T06:40:22

adding scope_id parameter to the end solved my problem. Final parameters should be like this.

OracleParameter[] parameters = new OracleParameter[] {                    
                new OracleParameter(":CATEGORY",OracleDbType.Varchar2,30, cfgRoleScope.CATEGORY, ParameterDirection.Input),
                new OracleParameter(":TYPE_CODE", OracleDbType.Varchar2,30, cfgRoleScope.TYPE_CODE,ParameterDirection.Input),
                new OracleParameter(":LOCATION_ID",OracleDbType.Long,10, DBNull.Value, ParameterDirection.Input),
                new OracleParameter(":OBJECT_ID", OracleDbType.Long,10, DBNull.Value, ParameterDirection.Input),
                new OracleParameter(":NAME", OracleDbType.Varchar2,50, cfgRoleScope.NAME,ParameterDirection.Input),
                new OracleParameter(":DESCRIPTION", OracleDbType.Varchar2,50, cfgRoleScope.DESCRIPTION,ParameterDirection.Input),
                new OracleParameter(":UPDATE_DATE",OracleDbType.Date,cfgRoleScope.UPDATE_DATE,ParameterDirection.Input),
                new OracleParameter(":UPDATED_BY",OracleDbType.Long,6,cfgRoleScope.UPDATED_BY, ParameterDirection.Input),                    
                new OracleParameter(":SCOPE_ID", OracleDbType.Long,10, cfgRoleScope.ID, ParameterDirection.Input),

            };

ORA-00932: inconsistent datatypes: expected NUMBER got DATE

1 Answers