parameter of OracleDbType.Double

parameter of OracleDbType.Double

Postby zhuqijun » Mon 10 Oct 2016 07:09

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?
zhuqijun
 
Posts: 23
Joined: Fri 05 Jul 2013 06:56

Re: parameter of OracleDbType.Double

Postby Pinturiccio » Tue 11 Oct 2016 12:57

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.
Pinturiccio
Devart Team
 
Posts: 1891
Joined: Wed 02 Nov 2011 09:44

Re: parameter of OracleDbType.Double

Postby zhuqijun » Tue 11 Apr 2017 08:00

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?
zhuqijun
 
Posts: 23
Joined: Fri 05 Jul 2013 06:56

Re: parameter of OracleDbType.Double

Postby Pinturiccio » Tue 11 Apr 2017 12:11

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";
Pinturiccio
Devart Team
 
Posts: 1891
Joined: Wed 02 Nov 2011 09:44

Re: parameter of OracleDbType.Double

Postby zhuqijun » Wed 12 Apr 2017 12:58

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?
zhuqijun
 
Posts: 23
Joined: Fri 05 Jul 2013 06:56

Re: parameter of OracleDbType.Double

Postby Pinturiccio » Thu 13 Apr 2017 12:58

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/library/system.decimal.maxvalue(v=vs.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.
Pinturiccio
Devart Team
 
Posts: 1891
Joined: Wed 02 Nov 2011 09:44

Re: parameter of OracleDbType.Double

Postby zhuqijun » Fri 14 Apr 2017 02:29

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?
zhuqijun
 
Posts: 23
Joined: Fri 05 Jul 2013 06:56

Re: parameter of OracleDbType.Double

Postby Pinturiccio » Fri 14 Apr 2017 14:18

Yes, it is a correct way.
Pinturiccio
Devart Team
 
Posts: 1891
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle