Which .NET data type is best for mapping the NUMBER Oracle data type in NHibernate?

Question

I've seen some examples in which decimal is used in NHibernate projects for mapping to whole number columns in Oracle. Right now I'm using int and long in my program.

What are the advantages of decimal over int/long? Does it perform better?

Solution 1

I've seen decimal used instead of int/long in various examples. I'm just trying to understand why

That's probably because .NET decimal and Oracle NUMBER maps a bit better than long and NUMBER and it also gives you more flexibility. If you at a later stage add a scale in the Oracle column then you wouldn't have to change datatype if you already used decimal.

decimal is certainly slower than int and long since the later two are supported in hardware. That said, you have to crunch some serious amount of data for it to make any difference. I still think that you should use long if that that's what you're dealing with and then you should also let the table column definitions represent that. NUMBER(18,0) for long and so on.

The reason decimal maps a little better is that long is 64 bits and decimal is (kind of) 128 bits.

.NET

Type: decimal
Approximate Range: ±1.0 × 10^−28 to ±7.9 × 10^28
Precision: 28-29 significant digits

Type: long
Range: –9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
Precision: 18 (19 for ulong) significant digits

Oracle

NUMBER defaults to 38 significant digits and scale 0 (integer).

Type: NUMBER
Range: +- 1 x 10^-130 to 9.99...9 x 10^125
Precision: 38 significant digits

Microsoft is aware of the problem and notes

This data type is an alias for the NUMBER(38) data type, and is designed so that the OracleDataReader returns a System.Decimal or OracleNumber instead of an integer value. Using the .NET Framework data type can cause an overflow.

Come to think of it you actually need BigInteger to be able to represent the same number of significant digits as to what NUMBER defaults to. I've never seen anyone do that and I would suppose it's a very rare need. Also BigInteger still wouldn't cut it since NUMBER can be of positive and negative infinity.

Solution 2

[.NET: Int32] = [Oracle:NUMBER(2)..NUMBER(9)*] 
[.NET: Int64] = [Oracle:NUMBER(10)..NUMBER(18)*]
[.NET: Double] = [Oracle:NUMBER(x, 0)..NUMBER(x, 15)*]
[.NET: Double] = [Oracle: FLOAT]
[.NET: Decimal] = [Oracle:NUMBER]  

Solution 3

NUMBER(1,0)     => Boolean          
NUMBER(5,0)     => Int16/short.MaxValue == 32767    
NUMBER(10,0)    => Int32/int.MaxValue == 2,147,483,647    
NUMBER(19,0)    => Int64/long.MaxValue == 9,223,372,036,854,775,807    

Comments

[Ilya Kogan]

I don't know where you took this from, but this is not what happens de facto. From what I see, double is mapped to DOUBLE PRECISION, int is always mapped to NUMBER(10,0) and decimal can be mapped to any NUMBER(x,y) depending on the .Precision(x).Scale(y) settings. By the way, chaning the .Precision of int doesn't change the mapping - it's still NUMBER(10,0). And besides, this doesn't answer my question.

[Homam]

I use Devart, I read the following answer which describe the CSDL mapping types between Oracle and .Net .devart.com/forums/viewtopic.php?t=15604

[Phill]

Learn something new every day :)

[sandeep talabathula]

//This makes lot more sense to me. Thanks a lot Jonas for this great explanation...!

[quetzalcoatl]

In pinch, you can always use map to Oracle.ManagedDataAccess.Types.OracleDecimal as hinted in docs.oracle.com/cd/B28359_01/win.111/b28375/featTypes.htm - IIRC works with Number(38) and handles +/-INF cases.

[PeterB]

It depends on the way you lock at the Problem: If you want to store an int32 (max = 2,147,483,647) you need a NUMBER(10,0). If you want to read a NUMBER(10,0) (max = 9,999,999,999) you need a long. Since the boundaries follow completely different concepts, there is no way around that. You have to know where the data comes from and maybe use additional db constraints.