C# - ODP.NET and ora-01475 must reparse cursor to change bind variable datatype

Question

Hi all,

I'm getting ora-01475 whenever I try to insert a null value in a column (of type DateTime) after some records have already been inserted that have real date values. I'm using the OracleParameter constructor that takes the name and the value as an object (I assume the data type is then implied from the datatype of the object), but since sometimes the value of my parameter is null, it's being set as a String, therefore throwing this error. I don't want to use the constructor that takes the datatype explicitly because I use reflection heavily to build the OracleCommand object and its parameters.

How can I reparse the cursor (as the error suggests) if I find this situation? Has anyone else run into this and has a solution? I'd highly appreciate any help.

Thanks!!

Ricardo.

Answer 1

Have you tried to use nullable types?

            DateTime? myDate;
            //Code to set myDate value...
            string sql = "[your SQL]"
            using (OracleCommand command = new SqlCommand(sql, cn))
            {
                OracleParameter param = new OracleParameter(":Name",myDate);
                command.Paerameters.add(param);
                command.ExecuteNonQuery();
            }