CODEWITHSUNDEEP
.netoracle-databaseado.netoledbodp.net
Ned
Ned

Reputation: 1207

.NET code adds a trailing 0 to the numbers with 1 or 3 fractional digits stored in Oracle DB

I run a SELECT query in .NET (using OracleDataAdapter) and SQL Developer. Result sets are slightly different:

SQL Developer returns:

enter image description here

.NET returns:

enter image description here

As you see, there is an extra 0 at the end of the values that have 1 or 3 digits after dot. What is the root cause of this problem and how to prevent it?

We can easily remove the extra 0 in code but we would like to prevent it by fixing the underlying issue. Thanks!

UPDATE (1/29/2018):

Data type in Oracle: enter image description here

This is the .NET code to fetch the data:

OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString);

if (conn.State != ConnectionState.Open)
    conn.Open();

DataSet hereDataSet = new DataSet();

try
{
    using (OracleCommand oCmd = new OracleCommand())
    {
        oCmd.Connection = conn;
        oCmd.CommandText = "select V_COLUMN from T_TABLE where V_COLUMN='123'"; 

        OracleDataAdapter oDataAdapter = new OracleDataAdapter(oCmd);

        oDataAdapter.SelectCommand = oCmd;
        oDataAdapter.Fill(hereDataSet);
    }
}
catch (Exception ex)
{
    // exception logic
}
finally
{
    if (conn.State == ConnectionState.Open)
    { conn.Close(); }
}

Upvotes: 2

Views: 399

Answers (3)

Ned
Ned

Reputation: 1207

I created a support ticket to Oracle. They told me that it is a BUG

Upvotes: 4

Ctznkane525
Ctznkane525

Reputation: 7465

Oracle stores numbers differently. Are you sure that the number you are getting back from oracle when going to .net decimal isnt 33641.70000000000001 for example. You might want to get one row with a debugger into a regular variable and hover over it (not ToString) to find out...if that is it, your solution since you want only 3 decimal places would be to explicitly round to 3. I've had to do this before.

Upvotes: 0

Jeremy Thompson
Jeremy Thompson

Reputation: 65574

As per the MSDN documentation: https://msdn.microsoft.com/en-us/library/fzeeb5cd.aspx

If format is null or an empty string, the return value of this instance is formatted with the general numeric format specifier (G).

I think what you want to do is:

var asExpected = oracleDataReader.GetDecimal(0).ToString("G0");

Upvotes: 2

Related Questions