Oracle Int/Decimal problem

[Egil Winther]

Using OracleDataAdapter we have a problem with integers.

The following table can be used as an example:

CREATE TABLE tCmpCommunic (
nCommunicKey INTEGER NOT NULL,
nPartyKey INTEGER NULL,
nContactMechanismType INTEGER NOT NULL,
nGeoKey INTEGER NULL,
sAddress NVARCHAR2(1000) NULL,
sComment NVARCHAR2(100) NULL,
nComViaGeoKey INTEGER NULL,
bMainContact SMALLINT NULL
);


For some reason, the integer-columns are returned as decimals when using the
Fill-method on
the dataadapter.

Has anyone seen this before? Are there something we are missing?

Thanks in advance

Egil Winther
ShipNet AS

 

[Frans Bouma [C# MVP]]

Using OracleDataAdapter we have a problem with integers.

The following table can be used as an example:

CREATE TABLE tCmpCommunic (
nCommunicKey INTEGER NOT NULL,
nPartyKey INTEGER NULL,
nContactMechanismType INTEGER NOT NULL,
nGeoKey INTEGER NULL,
sAddress NVARCHAR2(1000) NULL,
sComment NVARCHAR2(100) NULL,
nComViaGeoKey INTEGER NULL,
bMainContact SMALLINT NULL
);


For some reason, the integer-columns are returned as decimals when using the
Fill-method on
the dataadapter.

Has anyone seen this before? Are there something we are missing?

You should define your columns using the NUMBER type and specify a
precision and scale. INTEGER is an alias for NUMBER, though it might be
that the actual number stored in the field is too big to be kept in a
..NET integer and is returned as a decimal.

For example, ODP.NET returns NUMBER(20,0) as Decimals too, though
NUMBER(16,0) as Int64's.

FB

--

 

[Guest]

First off, INTEGER is an alias for NUMBER with a precision of 0. The
OracleClient classes do not distinguish betweeen a NUMBER that is an INT and
a NUMBER that is not. By changing INTEGER to NUMBER(##,0), you might get an
integral cast. If not, I believe a move to ODP.NET is in order (free from
http://otn.oracle.com).

Second, to map Oracle to SQL integral types, even with ODP, there is a size
limit. I believe it is 16, so large INTEGER types will still map to floating
point.

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Egil Winther]

Thanks Frans and Gregory for your help.

Will try to implement your suggestions.

Egil Winther
ShipNet AS

 

[Frans Bouma [C# MVP]]

First off, INTEGER is an alias for NUMBER with a precision of 0. The
OracleClient classes do not distinguish betweeen a NUMBER that is an INT and
a NUMBER that is not. By changing INTEGER to NUMBER(##,0), you might get an
integral cast. If not, I believe a move to ODP.NET is in order (free from
http://otn.oracle.com).

That's scale, not precision

Second, to map Oracle to SQL integral types, even with ODP, there is a size
limit. I believe it is 16, so large INTEGER types will still map to floating
point.

no, to decimals (38,0). Here's a table.
Precision 0 -> actual precision = 38 (oracle default)

Scale = 0:
actual precision < 5 -> System.Int16
5 <= actual precision < 10 -> System.Int32
10 <= actual precision < 19 -> System.Int64
actual precision >= 19 -> System.Decimal(precision, 0)

Scale > 0:
actual precision < 8 -> System.Single
8 <= actual precision < 16 -> System.Double
actual precision >= 16 -> System.Decimal(precision, scale)

FB

--