Solved

Oracle ODAC GetDouble returning extra precision

Posted on 2013-11-07
58
876 Views
Last Modified: 2013-12-12
while (dr.Read())
                {
                    Console.WriteLine(dr[2]);
                    double dd = Convert.ToDouble(dr[2]);
                    Console.WriteLine(dd);
                    dd = dr.GetDouble(2);
                    Console.WriteLine(dd);
                }

When reading a field which is type NUMBER(14,2) in Oracle, where the stored value is 2077.49, when I retrieve the value with Oracle.DataAccess.dll file version 2.112.3.0 querying Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production,

The value I get back is 2077.4900000000002.

This causes a problem when matching this number to other values.

Any tips as to where I might be going wrong?
0
Comment
Question by:gswitz
  • 37
  • 16
58 Comments
 

Author Comment

by:gswitz
ID: 39631690
We are getting invalid cast when the value to cast to double is 0.

This is the .Net 3.5 framework.
0
 
LVL 44

Accepted Solution

by:
AndyAinscow earned 500 total points
ID: 39632919
First - I don't know oracle.

>>When reading a field which is type NUMBER(14,2) in Oracle, where the stored value is 2077.49

Are you certain this is storing to 2 decimal places or just displaying to 2 decimal places?  There is a difference.

Typically to compare floating point values one takes the absolute value of the difference and sees if that is within a limit.
eg. to compare to x
is the absolute value of (2077.4900000000002 - x) less than 0.0001 then they are the same value.
0
 

Author Comment

by:gswitz
ID: 39639017
I think that Oracle enforces a check constraint to make sure the number meets the requirements, but it always stores numbers in the same way.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39639032
I would still err on the side of caution and perform a check that the two numbers don't differ by more than a small amount rather than compare two floating point values by an equals operation.
0
 

Author Comment

by:gswitz
ID: 39639126
drop table temp_geoff;
create table temp_geoff (Numeric_TEST                   NUMBER(14,2));
grant all on temp_geoff  to public ;
insert into temp_geoff(Numeric_TEST) values (4000.99);
insert into temp_geoff(Numeric_TEST) values (0);
insert into temp_geoff(Numeric_TEST) values (2077.49);
commit;

To test the returnset, use the following in a c# console app that includes a reference to Oracle...
var connString = "Data Source= (DESCRIPTION =);User Id=zzUser;Password=zzPassword;";
DataSet ds = new DataSet();
OracleConnection conn = new OracleConnection(connString);
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select Numeric_TEST from temp_geoff";
cmd.CommandType = CommandType.Text;
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(ds);
conn.Close();
conn.Dispose();
ds.WriteXml(AppDomain.CurrentDomain.BaseDirectory + @"\ds_1.txt");
Process.Start("notepad.exe", AppDomain.CurrentDomain.BaseDirectory + @"\ds_1.txt");

These are the results regardless of which driver I choose...
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <NUMERIC_TEST>4000.9900000000002</NUMERIC_TEST>
  </Table>
  <Table>
    <NUMERIC_TEST>0</NUMERIC_TEST>
  </Table>
  <Table>
    <NUMERIC_TEST>2077.4900000000002</NUMERIC_TEST>
  </Table>
</NewDataSet>

Oracle Drivers tested include
Oracle.DataAccess.dll v 2.121.1.0
Oracle.DataAccess.dll v 2.112.3.0
Oracle.DataAccess.dll v 2.112.2.0
0
 

Author Comment

by:gswitz
ID: 39639150
With due respect, Andy, it's important that we are able to get correct data back from Oracle.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39639240
I agree with you.  The problem is that I know floating point numbers are usually stored as approximate values and I suspect Oracle is doing just that.
0
 

Author Comment

by:gswitz
ID: 39639271
But it isn't created as an Oracle Float... this is just a number.

number(14,2)  -- returns bad values
number -- returns valid values
number(14) returns valid valudes
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39640834
>>But it isn't created as an Oracle Float... this is just a number.

Erm, why is 2077.49 not a floating point number ?  You do understand what a floating point number is I hope.

Integer values (eg. 42, 7, 12345678) are stored in the computer precisely.
Currency values (eg. £42.99) are AFAIK stored specially with the 4 digits after the decimal point actually as a separate integer value - ie. two integer values despite the decimal point.
Floating point values (eg. 2077.49 - note I don't mention any precision such as single or double) are stored as approximations.  

You might want to look up in eg. wikipedia if you don't know how computers work with numbers.



You might want to perform a little test.
Store something such 2077.123 then bring it back with your number(14,2).  I bet you get something like 2077.120000000002 - which is missing the .003, ie. trimmed down to 2 decimal places
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39640840
ps.  As I said I don't know Oracle specifically, I just know how the computer handles the different types of numerical values in general.  Try the test I suggested (and with some other floating point values), maybe this is in reality a bug.
0
 

Author Comment

by:gswitz
ID: 39641226
Andy,

You acknowledge you don't know Oracle and then talk down to me. It's a little frustrating.

Oracle has Float types and Number data types.
http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm

Numbers are stored as Numbers with precision and scale defined. That's what I'm doing.

create table temp_geoff (Numeric_TEST                   NUMBER(14,2));

I'm looking for someone who can run this test for me. I have clearly defined the test above.

I am also opening an Oracle SR.

I was hoping that SOMEONE who has Oracle and C# installed could execute my test above either with the same drivers and database or with different drivers and database and report the results.

Lecturing me on the meaning of a FLOAT is a joke.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39641294
I've had a look in the oracle documentation.
http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i16209

It is the case that within oracle the precision to 38 decimal places is guaranteed.  It looks like Oracle isn't behaving as I guessed so that isn't where your problem is coming from.  

HOWEVER, you are then reading this into the program and converting it into a double.  As I have kept saying a floating point number is an approximation.  

>>Lecturing me on the meaning of a FLOAT is a joke.
You are using this number in two very different environments - Oracle and C#.  Comparing floating point numbers for equality is a way to write bad programming code.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39641376
example about approximations using floating point numbers in code
0
 

Author Comment

by:gswitz
ID: 39641391
Nowhere in the C# source posted above do I convert to double. I just fill the dataset using the Oracle Data Adapter.

To test the returnset, use the following in a c# console app that includes a reference to Oracle...

var connString = "Data Source= (DESCRIPTION =);User Id=zzUser;Password=zzPassword;";
DataSet ds = new DataSet();
OracleConnection conn = new OracleConnection(connString);
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select Numeric_TEST from temp_geoff";
cmd.CommandType = CommandType.Text;
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(ds);
conn.Close();
conn.Dispose();
ds.WriteXml(AppDomain.CurrentDomain.BaseDirectory + @"\ds_1.txt");
Process.Start("notepad.exe", AppDomain.CurrentDomain.BaseDirectory + @"\ds_1.txt");

Open in new window

I appreciate your passion.
0
 

Author Comment

by:gswitz
ID: 39641401
@Experts-Exchange

I'm afraid that Andy in his enthusiasm to help is drowning my thread. Should I repost the same question and ask him not to post on it, or just be patient? Do you have a preference?
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39641484
>>Nowhere in the C# source posted above do I convert to double.

and from the question:
while (dr.Read())
                {
                    Console.WriteLine(dr[2]);
                    double dd = Convert.ToDouble(dr[2]);
                    Console.WriteLine(dd);
                    dd = dr.GetDouble(2);
                    Console.WriteLine(dd);
                }

Hmmm.  So I totally misunderstand what double dd and Convert.ToDouble and GetDouble are doing.  I thought they would involve the double data type.  

ps.  There is a request attention link by the question body - I've requested a moderator to come take a look.
0
 

Author Comment

by:gswitz
ID: 39641729
Please don't post on my next thread.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39641876
>>Please don't post on my next thread.

I'll try not to (lots of people ask questions) but I don't like remembering trivial information.


ps.  Other experts might well have pointed out exactly the same as I did.
0
 

Author Comment

by:gswitz
ID: 39704609
When using System.Data.Oracle, when you fill a dataset where the database fields are defined as number(14,2) you get back Decimals which can be compared. When using Oracle.Data, when you fill a dataset you get back doubles.

So what's the difference between the two? When filling a dataset from an Oracle data type where precision is defined on a number field, it uses FLOATs rather than Decimals.

Specifying to Use Native Types on the data adapter before the FILL is one possible work around to the problem. It greatly increases the XML for the Schema, but it does achieve the end.

I was not able to clearly communicate the problem to Andy and I just wanted him to stop as he was blocking me from getting the information I needed. I gladly awarded him points and asked him kindly not to post on my next thread.

I ended up getting some help on Experts Exchange in these threads...
http://www.experts-exchange.com/Programming/Languages/.NET/Q_28291918.html
http://www.experts-exchange.com/Programming/Languages/.NET/Q_28291973.html

This thread really wasn't helpful. I understand Andy just as I understand you. I was having trouble expressing my issue perhaps or you and he were having trouble hearing it. After changing from the System.Data.Oracle client to the Oracle. client, this problem was introduced. Andy was not able to be useful in helping me understand why.

I still don't know why when using System.Data.Oracle it gets one type out of Oracle and when using the Oracle native driver it gets a different type.
0
 

Author Comment

by:gswitz
ID: 39704613
BTW, Thermoduric, it DOES matter how Oracle stores the number in terms of the Oracle type returned.

For example Number(1) gets returned as a SHORT. Number(5,2) get returned as another type... Number(14,2) gets returned as a DOUBLE, Number(16,2) gets returned as a Decimal.

The more I think about this the more frustrated I'm getting that I've been officially dressed down in a public forum by a moderator when my points were valid.

Perhaps reaching out to me privately would have been prudent.
0
 

Author Comment

by:gswitz
ID: 39704615
Sorry it went so badly.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39705491
>>Sorry it went so badly.

From my side there is no ill feeling.  Everything went professionally and politely.

Rereading the question I don't see anything about using two different interfaces and it was specifically using doubles, no other data type in the C# code provided.  It seems like the question was not well written, unfortunately.  Nuff said, no point in flogging a dead horse.

>>For example Number(1) gets returned as a SHORT. Number(5,2) get returned as another type... Number(14,2) gets returned as a DOUBLE, Number(16,2) gets returned as a Decimal.

I guess (I don't know the details of the actual code) that the different types returned are based on the range (5, 14, 16) and the requirement it is floating point or integer (eg. 1 vs 5,2) that is specified.  A single has a smaller maximum than a double which itself has a smaller maximum as a decimal data type.  So my guess is the return type is reflecting what can suitably fit into that type - a number that is (possibly) outside the range of a single will not be returned as a single.
0
 

Author Comment

by:gswitz
ID: 39707904
I've requested that this question be closed as follows:

Accepted answer: 0 points for gswitz's comment #a39639126

for the following reason:

The answer to the problem was ultimately found through running this test. The answer to the problem is not included in any other post in this thread.
0
 

Author Comment

by:gswitz
ID: 39706805
It's fine to close this thread. There is no useful information in this thread.
0
 

Author Comment

by:gswitz
ID: 39707024
BTW, in an effort to clarify the problem I wrote a test in which there is no cast to DOUBLE. Just a data-adapter filling a dataset. When using System.Data.Oracle, there is no issue. When using the Oracle Drivers I listed in the test it implicitly casts to double when the numbers are defined with a certain precision. I don't know why this is or where it is documented. So far as I can tell, no one tried my test.

I feel I've been treated rudely and that I have not gotten help. I don't feel the issue I posted about (and I'll grant clarified after the initial post) was ever addressed in this thread.

I feel I have been rudely treated by Andy and Thermoduric.

I tried twice to grant Andy the points and asked only that he not post on my next thread so that I could get the help I was seeking since we were not communicating well. He was offending me and I obviously offended him.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39707905
Your comment #a39639126 contains rather different code/information as to what you originally asked a question about.  It also seems to just demonstrate your problem and is in no way a solution to it.  (If it was I would have expected you to indicate that when you posted that.)
It is difficult for me if you want to change the goalposts whilst things are in motion.

Basically you asked a question which was not what you wanted to know the answer to.  I have answered that original question and you did not seem to understand what I was telling you.  If I offended you when attempting to get my explanation across then please accept my apologies, it was not intentional.  

Bear in mind I can not read minds.  It is your responsibility to ask the question that you want answering, asking for something you do not want is not a valid reason for rejecting the answer to that question.



>>and I obviously offended him.
Offended, no.  Annoyed, Yes.
Claiming Nowhere in the C# source posted above do I convert to double. whereas the code posted did contain multiple references to operations involving the double data type.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:gswitz
ID: 39708160
>>Claiming Nowhere in the C# source posted above do I convert to double. whereas the code posted did contain multiple references to operations involving the double data type.

I was not taking the original post into consideration when I typed this. In the test I posted (6th post in the thread), there is no DOUBLE mentioned. Andy was continuing to dominate my thread without doing the test to understand the problem. I was getting frustrated that he was drowning my thread. He kept repeating himself in an unhelpful way. He was saying things that were true but not relevant and would not follow my instruction to understand that this was the case.

>>Bear in mind I can not read minds.

No one has to read minds. Did you run the test (post 6)? The original question does explain the issue (post 1).

There are some points of confusion related to it which I now understand which are not clarified yet in this thread so I will attempt to.

First, I believe you can do an equivalent comparison on Doubles cast using the same method.

Convert.ToDouble(2077.49)=Convert.ToDouble(2077.49)

As long as you use the same Convert.ToDouble function and perform no math as a double, the numbers should be equivalent. The function Convert.ToDouble should be deterministic (although I haven't actually tested this).

The problem originates with the fact that that when using ODAC (ODP.NET) to return the number 2077.49 when stored in an Oracle table where the field is specified as NUMBER(14,2), the number in the Oracle Data Reader or that will fill a dataset from a data adapter will be 2077.4900000000002. This does not use Microsoft's Convert.ToDouble at this stage. It is before this point. The Convert.ToDouble in the original post only added confusion because I didn't realize at the time it was a symptom of the upstream issue.

Oracle.DataAccess.dll is the introduced change in the application that is causing the change in behavior. When using System.Data.OracleClient, the number is returned as stored (2077.49). When using any of these DLLs...
Oracle.DataAccess.dll v 2.121.1.0
Oracle.DataAccess.dll v 2.112.3.0
Oracle.DataAccess.dll v 2.112.2.0
the value is returned as 2077.4900000000002 only when the Oracle field is specified as NUMBER(14,2). (There are other specifications that cause this, but beyond the scope of this explanation).

So, then why? What can I do to fix it?

http://docs.oracle.com/cd/E48297_01/doc/win.121/e38358/toc.htm
As defined here, it is possible to specify on a data adapter to use Oracle Native Types. This is a work-around only for filling the dataset. For reasons I cannot explain, the architects at my company have requested that we not use this work-around unless we are unable to change the field specification. For us, the answer to the problem is to

change the Oracle field specification for columns with a precision of 2 to Number(16,2) or above.
explicitly use Oracle's GetDecimal function where possible
Avoid directly filling datasets with Oracle's Data Adapter
As a last resort, use oraAdapter.ReturnProviderSpecificTypes = true;

http://docs.oracle.com/html/E10927_01/featSafeType.htm

What I still don't understand is WHY ODP.Net returns values differently from the way in which they were stored. I haven't read all of Oracle's documentation for ODP.Net, but I've got the feeling that they are laughing to themselves about the Microsoft Spec for the .Net component. They have caused me a real headache and I'm sure they would laugh and point out we should be using Java.

To tell the truth, I think I was annoyed with Andy and willing to grant him the points in return for his efforts if only he would be willing to stay off my next thread so that I could get the help I needed from someone who would be able to understand my issue. I did get that help, btw, in the threads I posted above. I think Andy was offended which is why he asked you to come into this thread.

The whole thing has been a tremendous waste of my time. You and Andy both. I'm not convinced either of you took the time to understand my issue or to help me.

I have now spent an enormous amount of time trying to explain what should have been evident by my sixth post. Others were able to understand and answer the issue. The problem is a major PITA for upgrading old applications from the obsolete System.Data.OracleClient to the ODP client.

>> If I offended you when attempting to get my explanation across then please accept my apologies, it was not intentional.  

Andy, this was irritating and offensive.
>>why is 2077.49 not a floating point number ?  You do understand what a floating point number is I hope.
>>You might want to look up in eg. wikipedia if you don't know how computers work with numbers.
0
 

Author Comment

by:gswitz
ID: 39708342
I've requested that this question be closed as follows:

Accepted answer: 0 points for gswitz's comment #a39708160

for the following reason:

Please, please, please, let me close this thread so it doesn't mislead others. I just want it gone. It was a huge and useless waste of time.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39708209
>>>> If I offended you when attempting to get my explanation across then please accept my apologies, it was not intentional.  
>>I just so completely don't believe you. You absolutely meant offense. You even threatened me.

Where, please indicate which comment and what the threat was.


ps. I asked a moderator to step in because of
http://www.experts-exchange.com/Programming/Languages/.NET/Q_28288426.html#a39641401
where YOU seemed to request that, but didn't know how to do so.
0
 

Author Comment

by:gswitz
ID: 39708219
Andy, my last post was written as though YOUR last post was from Thermo. I didn't notice you were back on the thread. Sorry for that. I was in the process of modifying my post when you answered back.

It was Thermo who was threatening me, not you.

And yes, at first, in the post you indicate, I was hoping someone who could run my test would chime in to the post. But since I'm a paid subscriber, and have been for ages, I figured I wouldn't wait for help, I'd just create another post and grant you the points for effort.

I appreciate that you wanted to help. In this case, I don't think you caught on to the real issue. You didn't run the test I gave you. Even suggested the same test down the line when the precise test was posted above.

ALL THAT I WANT IS TO CLOSE THE THREAD. I would gladly have given you the points, but Thermo won't let me unless I accept one of your posts as my solution.

I'm losing sleep over this stupid thing!!

I'm so super frustrated, I just can't stand it!!

Since none of your posts are useful given the problem, I just don't feel comfortable doing that. I was fine with granting you the points on a non-meaningful post, but apparently that's against TOS (who knew?).
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39708259
Thanks for the apology.

>>You didn't run the test I gave you.

I couldn't and can't.  I don't have Oracle and did indicate in my very first post that Oracle isn't an area of expertise for me.  On the other hand C# and number crunching in general is.


ps.  I don't know if you know how the site operates when there is a problem like this.  My understanding is that one (or more) moderators will inspect the question and comments.  Other experts in the area may well be asked for their opinion.  At the end of that there will be a consensus and action will be taken.  I am out of that loop.  I don't know which experts are in that loop.  This might be a waste of time for you BUT the opinion seems to be the question is worth keeping because of the fact that it is concerned with how floating point numbers are handled and that one can easily make mistakes in code by just thinking if 12.34 can be written exactly on paper then a computer will handle a floating point value of 12.34 precisely.  Unfortunately computers don't work that way.  This site wants to have a database of questions and answers hence the requirement that a relevant comment is accepted as an answer.  It is in the small print ( I know - who reads it ? ).
0
 

Author Comment

by:gswitz
ID: 39708319
Sure. I gladly apologize to you Andy for the whole thread. It's awful and frustrating.

The fact that you can't run the test is exactly why I wanted someone who could. That's why I was asking you not to follow onto my next thread.

While your information regarding doubles might have been helpful, theoretically, in this case it didn't speak to the problem. Someone who could run the test could speak to the problem.

Ultimately, an Oracle SR was opened and Oracle gave the work-arounds I listed. I didn't communicate directly with Oracle myself, but I was the liaison to the person who did.

I want to add that I would be pleased for any help from you on my future threads, I was only asking that you let someone else try to help me with this issue.

For me, the Convert.ToDouble was incidental and was never the focus of the thread. For me, the question was, why is Oracle returning the wrong information?

Do you follow me? I think you do at this point.

I still don't really know why Oracle is doing an implicit cast to Double inside ODP.Net. Again, I think they are mocking a Microsoft Spec by following explicitly despite problematic resulting behavior.

It's kinda too bad that the thread is so caustic. I participate heavily in forums and try very hard to be a positive, friendly, good, useful participant. Forums are invaluable to people with technical skills and needs.

I think that it is tempting for regular users of any forum to talk down to the seldom-seen users of the same forum. It's often a place where sparks fly.

I hope you can understand why I persisted to seek help beyond your explanation of DOUBLE. It wasn't personal. It wasn't that what you were saying about DOUBLEs was wrong. It was that it didn't address my particular problem.

I might think that the thread is worth keeping for the purpose of helping people converting applications from using System.Data.Oracle to Oracle's ODP.Net. This is a significant PITA that Oracle is not going to make go away. :-)
Lucky us.

For me, I'm embarrassed by the thread. Lots of people know who I am and I don't want the nature of the thread to impact me professionally. For this reason, I've asked for the thread to be struck from the record. I have a feeling it will not be the last time the question is posed, so you will have a ready answer next time you see it.

Consider this thread...
http://stackoverflow.com/questions/2684460/oracle-data-provider-to-clr-type-mapping

Some other examples are
Number = Decimal
number(3)=short
number(2,2) = float
through
number(7,2)=float
number(8,2)=double
through
number(15,2)=double
Number(16,2) and greater = decimal
0
 

Author Comment

by:gswitz
ID: 39708331
As a note to E-E, it would be helpful to be able to grant a user points without accepting an answer to the question. Basically, it would have been nice in this case to have an 'Abandon Thread' option when the communication is not going well or you've got the wrong fish on the hook. ;-) no offense, Andy.

I would have liked to be able to grant Andy the points without misleading other users. I have traditionally done that by just picking a response that isn't meaningful and granting the points on it. If this isn't allowed, there should be an alternative provided.

It is at least equally inaccurate to grant points to someone pretending that they answered the question correctly when the real issue was never addressed.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39708435
>>First, I believe you can do an equivalent comparison on Doubles cast using the same method.
>>Convert.ToDouble(2077.49)=Convert.ToDouble(2077.49)


Be very careful this is not a good idea at all.
How not to compare floating point values
0
 

Author Comment

by:gswitz
ID: 39708560
Raise it up. Let's have a hearing.

My answer is the correct one and I have posted it.
0
 

Author Comment

by:gswitz
ID: 39708588
Andy,
Whether comparing doubles is a good idea is mute to the question of why is Oracle returning a double value from a field stored as NUMBER(14,2) with the value 277.49.

Also, it looks like Convert.ToDouble is deterministic from this test...
            var r = 0;
            for (var i = 0; i < 99999; i++)
            {
                if (Convert.ToDouble(i/100) == Convert.ToDouble(i/100)) continue;
                r = 1;
                Console.WriteLine("i={0}",i);
            }
            Console.WriteLine(r == 0 ? "All cases are equal." : "Not all cases are equal.");
            Console.ReadLine();

Again, if there were a helpful post on this thread, it's mine. Sorry. I stand by it. I have followed the instruction from Thermo and taken my good time to post a valid response to the question and I have granted myself the points as I was instructed to do as one of my alternatives.

I will accept nothing short of an apology from Thermo.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39708860
>>Again, if there were a helpful post on this thread, it's mine.
I disagree.  I can only work with the information you supply, if it is not complete or correct then it restricts what I can do to assist.  If the question being asked is not the question you want answering then you have only yourself to blame when you get an answer that isn't of use.

Based on what was originally asked I have told you what was going wrong in your expectations of data storage in a double and how to handle comparison of floating point values reliably.
If you want to change the goalposts at a later time then things might not go as you would desire.

I'll accept a decision by an independent authority (eg. thermoduric).
0
 

Author Comment

by:gswitz
ID: 39709144
The question I asked IS the question I wanted answered.

>>The value I get back is 2077.4900000000002.
>>Any tips as to where I might be going wrong?

The reason for this is the one I posted. It has NOTHING to do with the Microsoft Convert.ToDouble function.
0
 

Author Comment

by:gswitz
ID: 39710695
I've requested that this question be closed as follows:

Accepted answer: 0 points for gswitz's comment #a39708319

for the following reason:

This post is the actual solution to the problem. The solution as it stands is to work around the behavior of ODP.Net which differs from  System.Data's same implementation for some reason. The work around is clarified in this thread. It was given by Oracle.

I'm not attempting to violate the Terms of Service. I was only trying to give Andy the points where he had not earned them.
0
 

Author Comment

by:gswitz
ID: 39710336
Andy, I have also requested the deletion of this post, as I have told you already. Please, don't block this.
0
 

Author Comment

by:gswitz
ID: 39710941
The question was not is 2077.4900000000002 a float value. That was obvious. The question is why is this value being returned from the database. It is NOT being stored as an Oracle Float or Double. See Oracle Data Types here.
http://docs.oracle.com/javase/tutorial/java/nutsandbolts/datatypes.html

I explained this to Andy in post 4 (my response to his initial assertion).
http://www.experts-exchange.com/Programming/Languages/.NET/Q_28288426.html#a39639017
In post 4, when I say Oracle always stores Numbers the same way, I mean the Oracle data type Number.

In post 5, Andy returned to his hope that criticizing the way the app is designed will some how help. He's got a hammer. This HAS to be a nail.

So, in post 6, I spell it out, leaving all DOUBLE specifications out of the script. I create a Number, I store the specific numbers, I fill a dataset. The dataset contains Double values. Now, I take it a step farther and define ODP driver versions tested.

In post 7, I restate to Andy that the goal here is to get the correct data back from Oracle. I stored the number in a column typed NUMBER(14,2). When I use Oracle ODP to get the data, that is what I expect, not a Double.

Then Andy, who try the test I posted, and didn't say so, responds that Floating Point numbers are usually stored as approximate values. This makes me think he is definitely NOT understanding me. Didn't I say repeatedly now that the it is stored as a NUMBER? Didn't I even write a script to reproduce the problem? I'm thinking now that I can't get someone capable of helping because of Andy.

So, I say it more explicitly to Andy in the next post. "But it isn't created as an Oracle Float... this is just a number."

Next, Andy gets snarky. "Erm, why is 2077.49 not a floating point number ?  You do understand what a floating point number is I hope." The guy is trolling my thread and will not let it go. He isn't running the test. He isn't helping. He won't shut up. Ironically, he suggests exactly the test which I had already posted. So, proof positive he isn't reading my posts or just doesn't get it.

BTW, if you run the test in post 6 using System.Data you get back the correct numbers. Doesn't matter if you use System.Data.Oracle or .ODBC.

In the next post, he says, "Try the test... " Andy, do you mean the test script I posted in post 6? You want me to try that test??

So I try again with Andy and let him know that he's frustrating me. "You acknowledge you don't know Oracle and then talk down to me. It's a little frustrating." I explain Oracle Types again and give him a link. I explicitly state that I'm looking for someone to help with the test. At this point, btw, the architecture team at my company has repeated the issue and believes this to be an Oracle bug and begins the process to open an Oracle SR. Here at experts exchange I'm still be lectured on what a Float/Double is. I call again for SOMEONE to please execute the test. I make it clear that I don't want more posts about what Floats are or how they work. "Lecturing me on the meaning of a FLOAT is a joke."

Next, Andy asserts that my problem is coming when I'm converting it into a double.
When you execute Convert.ToDouble(2077.49) using Microsoft's Convert, you do not get the number 2077.4900000000002. But Andy thinks that's my problem. Here we start down another rabbit hole. Andy appears to think that executing Convert.ToDouble is not deterministic. I haven't caught on to this yet, but I eventually get to the place where I include a script to show him that it is. The Microsoft Convert.ToDouble function always returns the same value for the same input. Andy then criticizes the code for comparing for equality. This isn't the point. Don't you see?? He's missing the point.

Next, I emphasize that Nowhere in the C# source posted above (Post 6) do I convert to DOUBLE, and I quote the source for clarity. To be nice, I tell Andy, "I appreciate you passion." What I mean is, "Thanks for playing, please let someone else help."

I then post to E-E unsure how to get someone on the thread who might be willing to run the test. I also Call E-E to cancel my subscription because I really don't get much help from E-E any more and this thread is more proof that it isn't a good use of $ for me. E-E encourages me to hang with it and I do.

Andy next responds back with my original post. His post is not designed to be helpful. He has misunderstood the question and has started attacking me directly.

My patience exhausted, I ask him outright not to follow me to my next thread and close this one giving him points for trying without giving him points for answering. I know that this was a violation of TOS. Whatever. No one else was on the thread. No one else was helping. I didn't know about the delete thread option or I would have pursued it. I wanted to reward Andy for trying. He seemed like a nice guy before this.

Andy says he won't Troll me on my next thread and defends himself. Andy, I totally agree that LOTS of people would have pointed to the Doubles as being the issue. The issue isn't whether the number is a double or float or whatever. It's WHY is it. Do you see?

Then you step in. Welcome to the party, Themo!! :-)
You start by saying "It doesn't matter what Oracle does to store the number..." and Thermo, you're wrong. when using ODP to retrieve data from Oracle, the default casting being applied behind the scenes is wrong. That's the WHOLE problem. It DOES matter how the number is stored. The type of storage is what Oracle ODP is using to determine some sort of implicit cast. I don't know why. By the time you post this, I have tested a huge number of variants to determine the types.

On my new post,
http://www.experts-exchange.com/Programming/Languages/.NET/Q_28291918.html
I had immediately gotten my answer from joriszwaenepoel and I had discovered how to determine what different numbers were going to be implicitly cast in what way based on HOW THEY ARE STORED in Oracle. By this I mean that depending on what the check constraint is in Oracle on the number column that will be random casting applied...
Number = Decimal
number(3)=short
number(2,2) = float
through
number(7,2)=float
number(8,2)=double
through
number(15,2)=double
Number(16,2) and greater = decimal
Finally, thanks to my other thread, I have a way to determine how Oracle ODP is implicitly casting. I don't know why, but I can begin to document WHAT ODP will do so we can work around it.

Thermo, the bug in the code came when switching from System.Data.Oracle to ODP. The bug in the code is not related to comparing Doubles. The problem is not getting the value I stored in the DB back from the DB. So, at this point, I figure you're one of Andy's homies and didn't bother to read the thread or execute the test either. Did you execute the test before posting your snarky post? You were a presumptuous jerk, Thermo. After all the effort I put into the thread to come down on me without bothering to understand was rude at the least.

But you're new to the thread, so I start all over you. This time, I already know the answer. We've had Oracle on the phone. It should be easy to spell it out for you, right? NOT!

After trying to bring you up to speed (you'll read the posts I take the time to write DIRECTLY to you, I hope!!), I apologize for the whole thread. "Sorry it went so badly."

Andy responds that Everything went "professionally and politely". I'm glad he thinks so. I don't feel professionally treated. Andy you were intentionally sniping me in the threads above. Thermo, you sniped in your first post (and almost EVERY subsequent post). I do not share your feeling that this has been either professional or polite.

Thermo, "I'm not really sure how much clearer I can make this for you!" It's totally clear to me Thermo. It's you who is the problem.

Right now, I'm too tired and upset to keep going with the play by play. It's awful and miserable. It culminates in Andy saying he thinks the thread should be kept because his contributions are so useful. I'm using restraint to hold back on analogies and snipings of my own regarding keeping this thread. If you do, I will certainly consider that the only valid reason to do so is because you and Andy are just flat mean. Unhelpful and mean.

I don't know any other way to say it.

I have asked as nicely as I can. I have even answered my own question (using help from other posts on E-E and an Oracle SR, but NOTHING from Andy or Thermo). My answer was to my original question. I did not "move the goal posts".
0
 

Author Comment

by:gswitz
ID: 39710999
Why do you want Andy to get credit so badly?

In post 13, one if his last trying to help he says, "HOWEVER, you are then reading this into the program and converting it into a double.  As I have kept saying a floating point number is an approximation."

The problem does not have to do with this. If ODP.Net was not doing an implicit cast to Double using Oracle's GetDouble first, there would be no problem. The Convert.ToDouble is not introducing a problem. Andy did not get on to the mapper, and neither did you, before I posted that information from the other threads.

"You are to close this question appropriately based upon the guidelines I've already provided you."
I have tried and tried to close this appropriately. It is my opinion that Andy was not able to help me with my problem. He didn't say things that were wrong. But he didn't answer the question either. Why am I getting back 2077.4900000000002.

I will not close this question granting him the points because you threaten me. It's unreasonable. I feel I've been pretty clear.

I was willing to give Andy points for trying. At that time, I could just as easily have selected his attempted answers as his comments, but I didn't. Now, however, you are bullying me into it, and I won't be bullied. Andy didn't answer the question. That's how I see it.
0
 

Author Comment

by:gswitz
ID: 39711006
Even you are pointing out that the problem is related to the mapping.

Right??

" what it is being mapped to by the data adaptor and that is why you have a problem"

And where did Andy say this?

Why is the data-adapter mapping to a Double when it is stored as a Number (read decimal)?

How can I see what random data-types different Oracle column types might be mapped to?

It doesn't ALWAYS map to Double. Right? Number columns depending on specification in Oracle are being randomly converted to all kinds of types.
0
 

Author Comment

by:gswitz
ID: 39711010
"Have you considered that you could close this out by accepting Andy's perfectly valid points and your own comment that adds additional context? "

Just because Andy said something that's true doesn't mean it was helpful or addressed the question.

For example, he could have pointed out that, well, it's a number!

Right, I know it's a number. He would have been right of course, but not helpful. Why is it THAT number at THAT point? Asserting that it's a number is true, but not helpful and does not address the question.

Would you be insisting I accept Andy's perfectly valid point that it's a number?
0
 

Author Comment

by:gswitz
ID: 39711014
"Andy's observations are valid even if they are not the solution. "

You said it, brother!
0
 

Author Comment

by:gswitz
ID: 39711022
Look, I'm not the one asking for mediation. I don't need help anymore.

My question is answered (by others and me).

As you point out, Andy said things that were true and related to my question. I tried to grant him the points and close the ticket.

YOU re-opened it and insisted that I had to post the answer myself if the answer wasn't in the thread.

So I did. I've typed a freaking book here!

I've accepted my answer. If I've ticked the wrong box it was not on purpose.

Just because you threaten me, I will not go back and say Andy answered the question by defining Double. You said TOS requires me to accept the answer to the question or delete the question.

I'm willing to do either. I have posted and accepted the answer. I have requested the thread be deleted.

Why do you say I haven't done these things?
0
 

Author Comment

by:gswitz
ID: 39711036
I don't want to need to have this further reviewed.

What I want is to get the thread closed and deleted.

What I won't do is mark Andy's posts as the answer. (I should say I'm no longer willing to do this because I originally marked one of his posts as the answer and granted him the points so we could put it behind us).

How do we go forward?
0
 

Author Comment

by:gswitz
ID: 39711056
"On this occasion, I'm sorry but Andy's comments are not off the mark. They may not be your solution but they do point you in the right direction as to the cause of the problem."

Your perspective is that Andy helped.

He wouldn't run the test. He didn't listen to me. From his first post I was trying to bring him around. Who knows, he could have had my answer, he just didn't.

He never let go of the thread even though he couldn't help me. I was wanting the thread to go quiet so someone who could help me could chime in.

This is like one of those cases where you get the wrong guy in Support on the phone and you just call back and ask the identical question (post 6 was post 1 on my next thread verbatim) and you get a person who can help.

Perhaps I should have moved on faster than I did. I have seen threads where more than one expert chimes in. I guess I was hoping for that. I felt that Andy's insistence was preventing me from getting that.

So, is e-e like this now?

The new e-e? Everyone who participates gets a trophy?

Here's a question...
Want to do a deal?

If you will delete this thread after I grant Andy the points, I will do it.

I'll trust your word is good (you did say you're from the UK and those blighters are true blue). (I hope I used blighter in the correct and inoffensive way).

Andy, you on board? I can delete the thread after I grant you the points?
0
 

Author Comment

by:gswitz
ID: 39711091
This just has to be extortion.

http://en.wikipedia.org/wiki/Extortion
0
 

Author Comment

by:gswitz
ID: 39711187
Really, the problem is in ODP.Net.

It doesn't exist using System.Data anything (Oracle, ODBC).

For some reason, when ODP.Net maps the data it chooses to do random type casting. We still don't know why. As far as I'm concerned. Oracle's ODP.Net is in Oracle's domain. It's an Oracle problem. Oracle may have written this to Microsoft's spec so the two giants can point fingers at each other if they want.

It's my problem because I'm looking at having to change column data types because of this behavior. It is our preferred solution to the problem to alter all columns specified with a precision of 2 to at least be (16,2).

So far as I know, no one has been able to explain why Oracle is doing this. I'm guessing at a M$ specification.

Really, if you want the points, Andy, tell me why Oracle does a Double cast in this case.

I think I remember doing a fillSchema using the Microsoft.System.Data driver and seeing the decimal data type (which would be correct btw in case you haven't been following all this).

So then, to get closer to really explaining why, you could explain why the two fillSchema methods produce different results. What's causing that? If you can answer that, you might also be able to answer why the datareader itself produces different results (probably the same reason).

I'm sure there is a reason. We had an Oracle Bug number, but I believe that Oracle closed it as 'not a bug'. Which means that it's by design. To convert a decimal to double or float seemingly arbitrarily would never have been something Oracle would do just to spite us. There's a reason for it.

To answer why I get  2077.4900000000002, really, all of these sub-questions are relevant.

Question 1: Why does Oracle return a stored decimal as a double.
Answer 1: Because ODP.Net is doing an implicit conversion.
Question 2: Why is it doing an implicit conversion.
Answer 2: FillSchema shows the column maps to a double type when the Oracle column is type Number(14,2).
Question 3: Why do these DLLs cause a mapping to data types different from those in the DB
Oracle.DataAccess.dll v 2.121.1.0
Oracle.DataAccess.dll v 2.112.3.0
Oracle.DataAccess.dll v 2.112.2.0
where System.Data.Oracle does not?
Answer 3: Who knows.
Question 4: How can you work around it?
Answer 4: Stop using .Net, stop using Oracle (or try your best to catch it every possible place it could bite you).
0
 

Author Comment

by:gswitz
ID: 39715675
I've requested that this question be closed as follows:

Accepted answer: 0 points for gswitz's comment #a39711187

for the following reason:

Please?
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39711233
>>Andy, tell me why Oracle does a Double cast in this case.

I have said I will accept the third party decision about this thread.  I am making no more technical input.
0
 

Author Closing Comment

by:gswitz
ID: 39715676
Because I'm being forced to.
>> your account will be suspended
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now