parameter of OracleDbType.Double

[zhuqijun]

Our product is using DotConnect for Oracle 9.1.45.

The table is like below:
CREATE TABLE TestTable
(
ASSETATTRREAL NUMBER
)

The c# code is like below:

OracleConnection con = new OracleConnection();
try
{
con.ConnectionString = "user id=xxxx;password=xxx;server=serverxxx;Unicode=true;connection timeout=30";
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
con.Open();
cmd.CommandText =
"update TestTable set ASSETATTRREAL = :ASSETATTRREAL";

cmd.Parameters.Add("ASSETATTRREAL", OracleDbType.Double).Value = 2.05;

cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
con.Close();
}

My expectation is after run the c# code, the value of ASSETATTRREAL will be 2.05, but in fact it is 2.0499999999999998. my question is: is it a correct behavior?

[Pinturiccio]

This is the designed behaviour. You should use a parameter with the OracleDbType.Number type for a column with the NUMBER data type. OracleDbType.Double should be used for columns with the BINARY_DOUBLE data type.

You need to change OracleDbType of your parameter from Double to Number or change the data type in the Oracle table to BINARY_DOUBLE in order to solve the issue.

[zhuqijun]

Hello Support,

If we use oracleDbType.Number, we solved the precision issue.
But if we want to insert a very big data like 134340000000000000000000000000000
there will be exception:
Value was either too large or too small for a Decimal.
could you share some ideas how to solve?

[Pinturiccio]

Please provide a snippet of code where the issue is reproduced.

You can use the following code for creating the corresponding parameter:

Code: Select all

cmd.Parameters.Add("ASSETATTRREAL", OracleDbType.Number).Value = "134340000000000000000000000000000";

[zhuqijun]

Hi Pinturiccio
Great, now I can store a very big number by your example.
Could you share some knowledge why the big number can be store by converting to a string?

[Pinturiccio]

Oracle Number data type can store up to 38 decimal digits. At the same time, System.Decimal can store only values up to 79,228,162,514,264,337,593,543,950,335 which is 29 decimal digits. For more information, please refer to https://msdn.microsoft.com/en-us/librar ... .110).aspx.
When you create System.Decimal with a value 134340000000000000000000000000000 (33 digits), the System.Decimal generates the exception "Value was either too large or too small for a Decimal". The only way to pass a value of more than 29 digits to a parameter is passing the value as a string.

[zhuqijun]

Thanks for your reply.

I found another way by using OracleNumber as below:

Code: Select all

  
     double? v = 134340000000000000000000000000000D;
     OracleNumber n = new OracleNumber(v.Value);
     cmd.Parameters.Add("ASSETATTRREAL", OracleDbType.Number).Value = n;

It also can store the big number. Do you think it is also a correct way?

[Pinturiccio]

Yes, it is a correct way.