1

I have an Oracle table with a column of type NUMBER, that contains a range of floating point numbers. What is the correct way to read that into a C variable using Pro*C I have tried the following:

EXEC SQL BEGIN DECLARE SECTION;
static  float   o_start_x;
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT start_x
FROM  my_table
INTO  :o_start_x;

more often that not this is fine, however some floating point numbers, particularly those really close to 0 (e.g. 1.4E-43) causes the following errors:

ORA-01426: numeric overflow;

Is there a correct/safe way of reading such a value, or a method of having oracle convert the type safely enough, allowing for the loss of precision?

Tony The Lion
  • 61,704
  • 67
  • 242
  • 415
Colin Cassidy
  • 412
  • 5
  • 8

1 Answers1

2

float allows limited precision - double has more, 15 digits usually.

A caveat: floating point has issues when working with money, for example. Example: .10 cannot be exactly represented in IEEE-754 floating point internal representation of the data. A common workaround is to let oracle use BCD arithmetic, which avoids floating point issues, then read the final result into a double.

FLT_DIG
This is the number of decimal digits of precision for the float data type. Technically, if p and b are the precision and base (respectively) for the representation, then the decimal precision q is the maximum number of decimal digits such that any floating point number with q base 10 digits can be rounded to a floating point number with p base b digits and back again, without change to the q decimal digits.

FLT_DIG is normally six digits of precision minimum, DBL_DIG: 15.

As long as you avoid doing lots of math and compares in C code, unless you know how to deal with the issues I mentioned and other issues, getting a final result for money is easy.

EXEC SQL BEGIN DECLARE SECTION;
static  double   o_start_x;
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT start_x
FROM  my_table
INTO  :o_start_x;

If this number is gigantic, you may have to use a string. The limit for NUMBER is 32 digits of precision, which exceeds the limits for precision for common C datatypes. Pro*C does not support bignum datatypes, AFAIK.

jim mcnamara
  • 16,005
  • 2
  • 34
  • 51
  • `.10` _can_ be exactly represented in IEEE-754 decimal32, decimal64 and decimal128. `.10` _cannot_ be exactly represented in IEEE-754 binary16, binary32, binary64 and binary128. The binary formats are more common. – chux - Reinstate Monica Oct 14 '13 at 14:35
  • You're correct, decimalnnn is not commonly implemented in hardware. Can you name commodity hardware that has it? I cannot. Mainframe does. See: http://stackoverflow.com/questions/1447215/why-arent-floating-point-decimal-numbers-hardware-accelerated-like-floating-poi – jim mcnamara Oct 14 '13 at 15:38
  • My comment was not directed at hardware - but simply at the idea IEEE-754 and `0.1`. There are a number of decimal library implementations. Google-ing decimal64 came up with a number of them. As an Oracle system likely may require a speedy decimal implementation (i.e. HW), your assertion is likely correct within the scope of this post, thought not in general. The "Can you name commodity hardware ..." sounds like a good question to post as the 4 year-old reference you gave may benefit with 2013 info. – chux - Reinstate Monica Oct 14 '13 at 16:12
  • IEEE-754 2008 I think is the reference. – jim mcnamara Oct 14 '13 at 19:32