CODEWITHSUNDEEP
javasqloracle-database
John Auden
John Auden

Reputation: 23

Oracle ojdbc driver returns float result for integer values in NUMBER type column

I have Oracle 10g database and Java application based on Spring 4.1.6 framework, which extracts some data out of Oracle. I faced an issue, that ojdbc driver returns float result for integer values in NUMBER type column. ResultSet metadata identifies object type as BigDecimal.

Column definition: MINUTES NUMBER with no precision and scale Actual value is "70"

I have checked with Oracle 10, 11 and 12 ojdbc drivers and every time the same Java code returns "70.0000000000000000000000000000000000002 as value of this column in a row. I have also checked with Oracle SQL Developer 4.1.1 and it shows the same incorrect float value.

Toad however shows correct value "70".

Any ideas ? Please note again: **This issue is reproduced in Oracle SQL Developer 4.1.1 **

Update - Java code below : 

for (int j = 1; j <= count; j++) {
 Object field = resultSet.getObject(j);
 if (field != null) { 
      bufferedWriter.write(field.toString());
 }
 if (j < count) { 
       bufferedWriter.write(delimiter); 
 } 
}

Upvotes: 1

Views: 2755

Answers (1)

Utkarsh
Utkarsh

Reputation: 589

The NUMBER datatype stores fixed and floating-point numbers. When you specify numeric fields, it is a good idea to specify the precision and scale. This provides extra integrity checking on input.

NUMBER (precision, scale)

But, if a precision is not specified the column stores values as given i.e. maximum scale and maximum precision(up to 38 digits of precision). As given, it will behave as Float in java. Example of sample input given below :

Input              Stored As
7,456,123.89       7456123.89

A lot more info at Oracle Data Type.

Especially this section :

Internal Numeric Format

Oracle Database stores numeric data in variable-length format. Each value is stored in scientific notation, with 1 byte used to store the exponent and up to 20 bytes to store the mantissa. The resulting value is limited to 38 digits of precision. Oracle Database does not store leading and trailing zeros. For example, the number 412 is stored in a format similar to 4.12 x 102, with 1 byte used to store the exponent(2) and 2 bytes used to store the three significant digits of the mantissa(4,1,2). Negative numbers include the sign in their length.

So, If you want to store Integer value only, change the column definition to NUMBER(10,0) which is as close as you can get to Integer in java. Otherwise, strip the trailing zeros from BigDecimal

Code edited :

for (int j = 1; j <= count; j++) {
 BigDecimal field = (BigDecimal)resultSet.getObject(j);
 if (field != null) { 
    bufferedWriter.write(field.stripTrailingZeros().toString());
 }
 if (j < count) { 
     bufferedWriter.write(delimiter); 
 } 
}

Or Explicitly cast to Integer (to lose decimal part)

for (int j = 1; j <= count; j++) {
     Integer field = (Integer)resultSet.getObject(j);
     if (field != null) { 
         bufferedWriter.write(field.toString());
     }
     if (j < count) { 
         bufferedWriter.write(delimiter); 
     } 
    }

Upvotes: 1

Related Questions