[OT] Lack of IEEE values in Oracle

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in news:1004906388.28075.0.nnrp-14.9e984b29_at_news.demon.co.uk:

> Presumably you don't try to store those
> values in the database though. It would
> be a modelling error to do so. The fact
> that a hardware device has to report an
> error condition through a special value
> doesn't mean that a database used to
> hold the data should do the same.
>

Yes, we store them in the database, and doing so is not a modelling error. What we retrieve from the database must be guaranteed to be identical to what came in from the field -- even a single bit error can't be tolerated. If at any time the device sends a value (no matter how erroneous or unexpected), the database *must* be able to store that value, even if it is unexpected from the original design. In other words, I may never expect the values 1.0e-100 and 1.0e+1000, but if they show up, then I must store them for later retrieval -- an error message is not an accepatble option. The fact that the devices use the extreme portions of the IEEE range for indicators is only part of the problem.

The problem is (crudely) solvable in Oracle, but only if we do some serious bit manipulations -- split the double up into several integers and store those, and rebuild the double on retrieval. BTW, if you want to see some serious slowdown, try doing retrieving several million values with all that extra processing required. For that matter, all the standard SQL tools are no longer useful, since the processing stage has to be in place at all times.

-- 
Murray Peterson
Email: murray_peterson_at_shaw.ca (remove underscore)
URL:   http://members.shaw.ca/murraypeterson/