Oracle 11g -> Oracle Client -> Pro*C Program Double Precision representation

Question

A NUMBER(12,3) field in an 11g R2 Oracle db instance stores the number 271.448

It is FETCHed from a cursor into a double variable within a 32 bit Pro*C (version 11.1.0.6). preprocessed C program with Visual Studio 2010 being used for compilation of the .c files.

Printing the 271.448 (to stdout) immediately after it has been FETCHed into its double variable with fprintf and a format specifier of "%3.14f" gives: 271.44800000000004

Whereas if I do the following:

1. Select to_char(cast(271.448 as binary_double),'999.99999999999999') from dual;

2. As 1. but referencing the row/field in question rather than a number literal.

3. Select to_char(to_binary_double(271.448),'999.99999999999999') from dual;

4. As 3. but referencing the row/field in question rather than a number literal.

5. Assign 271.448 directly to a double in a non-Pro*C test C program and print to stdout with the %3.14f" specifier

6. Use http://www.binaryconvert.com/convert_double.html to do the conversion.

I get 271.44799999999998 for 1) -> 5) and 2.71447999999999979081621859223E2 for 6)

I anticipated the slightly inaccurate representation but not the inconsistency between Oracle Db and the Pro*C/C.

A few hours with google have made me marginally more knowledgeable overall, but no wiser as to my specific issue. Can someone educate me as to what I'm missing or what may be happening?

Answer 1

I'm not sure exactly what your question is: maybe, "why don't all the answers look like 271.448000000000000000000000000 ?"

But you're dealing with several representations:

• Oracle's internal NUMBER and its strange storage details (N.B.: OCI underpins Pro*C)
• C language double, usually:
• 64-bit IEEE754 double

And several conversion and/or formatting steps:

• to_char(cast(271.448 as binary_double),'999.99999999999999')
• to_char(to_binary_double(271.448),'999.99999999999999')
• double d = 271.448; printf("%3.14f\n", d);
• the "binary converter" site (or maybe use "%3.27f" as your format string)

I think the crucial difference here is Oracle's internal NUMBER format versus the typical C representation, so your core question may actually be along the lines of "why don't all these formats precisely store 271 + 56/125 ?" I'm not the person to provide this kind of answer - good thing we live in the age of search engines:

Why aren't rational numbers implemented and stored as fractions with zero loss of information?

or

Floating point numbers are a leaky abstraction