Invalid casting error when recurring division result is read using OracleDataReader

39 views Asked by At

OracleDataReader is throwing an error when the recursive division(say 1/3) result is returned. No error if the divisions are non-recursive(say 1/2). The error message is "Specified cast is not valid." The code snippet is as follows

string queryString = "Select (1/3) as R from dual";
                using (OracleConnection connection = new OracleConnection(oradb))
                {
                    OracleCommand command = new OracleCommand(queryString, connection);
                    connection.Open();
                    using (OracleDataReader reader = command.ExecuteReader())
                    {
                        ((Oracle.DataAccess.Client.OracleCommand)command).BindByName = true;
                        // Always call Read before accessing data.
                        while (reader.Read())
                        {
                            string s = Convert.ToString(reader[XmlConvert.DecodeName("R")], System.Globalization.CultureInfo.InvariantCulture);
                        }
                    }
                }
1

There are 1 answers

0
PinBack On

A number from Oracle is (usually) stored in a .Net Decimal number. However, the .Net Decimal data type can store only up to 28 digits accurately. Oracle can handle more than 28 digits.
If you read the number from your select, you will get an OverflowException or an InvalidCastException.
You can round the number and the select will work.

Select round(1/3, 28) as R from dual