Oracle 10g Float and Double Database Datatypes are not supported

[Guest]

Greetings,

I have data values that are inherently of type Double. Current we are using
Oracle Column Datatype NUMBER. When I create a TableAdapter in VS2005, the
database table column information is read from Oracle and a new Table is
created. However, the detected datatypes are always Decimal. Even if I
change the datatype in the Database to a lower precision that would not
exceed the precision of a Double, it is always detected as a Decimal. This
forces me to cast everywhere, making the strongly-typed dataset, not as
useful.

Option 1: Now you might wonder, why not just change the datatype after the
table is created. This does in fact work, but if you alter the Select
statement later, the table is regenerated and since the datatypes no longer
match what was found in the database, new columns are added with a "1"
appended to the column name, which wrecks everything. So, I have to delete
all the columns first, then make my SQL change, then go back and change all
the datatypes back to what I need them to be. This sucks.

Option 2: Use the new Oracle 10g BINARY_DOUBLE column datatype. This would
be the preferred solution, but Microsoft apparently does not support it.
Even though this may be perfectly matched with the precision of a
System.Double.

Option 3: Microsoft should detect the Precision and Scale of the NUMBER
datatype in Oracle and use the appropriate .NET datatype instead of always
using Decimal. I believe this is done is other MS products via ODBC, but not
here...

Question: Is there ANY numeric datatype I can use in Oracle that will be
detected by the the MS Oracle Data Provider as a System.Double in the
Strongly-Typed representation?

I fear this answer is no. If it is, Microsoft should provide a hotfix to
allow Option 2 or Option 3 to work. Without one of these solutions,
maintenance will be a big pain. Other developers on my team, should be able
to go into the Query Builder, check off a new column for the Select
statement, and this should just add the new column, but this doesn't work.
It makes a mess, and can go almost unnoticed until you build and a list of
cast exceptions appears... Hence the big pain.

-Valkyrie-MT

 

[Frans Bouma [C# MVP]]

Greetings,

I have data values that are inherently of type Double. Current we
are using Oracle Column Datatype NUMBER. When I create a
TableAdapter in VS2005, the database table column information is read
from Oracle and a new Table is created. However, the detected
datatypes are always Decimal. Even if I change the datatype in the
Database to a lower precision that would not exceed the precision of
a Double, it is always detected as a Decimal. This forces me to cast
everywhere, making the strongly-typed dataset, not as useful.

The MS Oracle provider always uses decimal for NUMBER(x,y) types,
despite the values of x and y. The provider of Oracle, ODP.NET does a
better job though. If you need these types, please consider using
ODP.NET. It also offers support for XmlType, something which isn't
supported by the MS oracle provider.

Question: Is there ANY numeric datatype I can use in Oracle that will
be detected by the the MS Oracle Data Provider as a System.Double in
the Strongly-Typed representation?

DOUBLE PRECISION should result in a Double.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Guest]

Frans,

You stated that "DOUBLE PRECISION should result in a Double." But an Oracle
column of type NUMBER(5) has less precision than a Double, but is always
interpreted as a Decimal.

Ultimately, in my opinion, this is a combination of two problems with
implementation by MS. First, the NUMBER type should be mapped to system
types that are the closest possible match without overflow. Second, when a
SQL statement in a TableAdapter is modified, new columns should not be added
if columns with the same name already exist. There are many properties that
can configured on the DataTable and if you add replacement columns just
because some of the properties were tweaked, this makes making any changes to
DataColumn properties almost worthless.

I could use ODP.NET, but it does not support TableAdapters.

Thanks,
-Valkyrie-MT

 

[Frans Bouma [C# MVP]]

Frans,

You stated that "DOUBLE PRECISION should result in a Double." But an
Oracle column of type NUMBER(5) has less precision than a Double, but
is always interpreted as a Decimal.

You should have read what I also said: the MS Oracle provider converts
ALL NUMBER(x,y) types into decimals, no matter what x, y is.

Ultimately, in my opinion, this is a combination of two problems with
implementation by MS. First, the NUMBER type should be mapped to
system types that are the closest possible match without overflow.
Second, when a SQL statement in a TableAdapter is modified, new
columns should not be added if columns with the same name already
exist. There are many properties that can configured on the
DataTable and if you add replacement columns just because some of the
properties were tweaked, this makes making any changes to DataColumn
properties almost worthless.

It's of course not in MS' interest to provide a rocksolid oracle
provider. It 'works' but you can better use ODP.NET

I could use ODP.NET, but it does not support TableAdapters.

I believe the 10.2 one does (with the vs.net tools)

FB

Thanks,
-Valkyrie-MT

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Guest]

Frans,

I understood what you said perfectly. My problem is that the two statements
are a bit contradictory. DOUBLE PRECISION should result in a Double, but in
the primary numeric datatype in Oracle, NUMBER, with double precision is a
Decimal.

I'm just trying to express my frustration with what I think is a design
issue here by MS. I get beat over the head with this sort of issue by
developers using Eclipse and Java 1.5 all the time. I think VS2005/C# is
better, but when there is a problem like this I have no recourse because
VS2005 is not open source... Then I have others tell me that it's not in
MS's best interests to fix it. Well, that's just unfortunate. Since VS2005
is not open source, the burden is on MS to make sure these issues are
addressed and resolved in a timely manner.

Oh well,
-Valkyrie-MT

 

[Frans Bouma [C# MVP]]

Frans,

I understood what you said perfectly. My problem is that the two
statements are a bit contradictory. DOUBLE PRECISION should result
in a Double, but in the primary numeric datatype in Oracle, NUMBER,
with double precision is a Decimal.

I know, but oracle has/had a synonym type called 'DOUBLE PRECISION'
which should resolve to a double. At least, in my mapping tables it
does for the MS provider. (I've to rerun the tests I did to re-check if
that's still the case though)

I'm just trying to express my frustration with what I think is a
design issue here by MS.

Well, I fully agree with you that this is very frustrating. In our O/R
mapper we support both, ODP.NET and MS' oracle provider, and every week
a couple of people wonder why the MS' provider returns Decimals for
NUMBER fields and ODP.NET doesn't. The sad thing is that ODP.NET is so
massive that installing it on a lot of desktops is sometimes a tough
call for people so they really want to use the MS provider but then of
course run into this silly issue.

I get beat over the head with this sort of
issue by developers using Eclipse and Java 1.5 all the time. I think
VS2005/C# is better, but when there is a problem like this I have no
recourse because VS2005 is not open source... Then I have others
tell me that it's not in MS's best interests to fix it. Well, that's
just unfortunate. Since VS2005 is not open source, the burden is on
MS to make sure these issues are addressed and resolved in a timely
manner.

Well, did you try ODP.NET 10.2 ? It should fix your problem

FB

Oh well,
-Valkyrie-MT

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Guest]

I couldn't agree more about the size of the Oracle Client. I'm looking into
the 'DOUBLE PRECISION' synonym now to see if I can use it to solve my problem
in a creative way. It looks like I can't directly have a table column type
be 'DOUBLE PRECISION', but there may be an alternate solution here... I use
both MS and ODP.NET Providers because they both offer distinct advantages...
I was really hoping for the types to resolve as they do in this article:

http://support.microsoft.com/kb/104977/en-us?spid=2509&sid=234

Only, precision >=15 is a Decimal.

Thanks for the feedback,
-Valkyrie-MT