Re: ALERT: ORACLE 8 NUMERIC PROCESSING ERROR

From: Peter Hanlon <pete_at_hanlon.co.uk>
Date: Sat, 21 Aug 1999 12:31:30 +0100
Message-ID: <935235217.16868.0.nnrp-04.9e98b813_at_news.demon.co.uk>


Your quite right about the precision of the float column. The problem seems to be caused by the Oracle 8.x server reporting back that float columns are actually number columns. When you create a table with a float(10) column and describe it, it will describe as a number(10). Since the float size is a binary size and the number is decimal, the size of the datatype is clearly not correct.

The impact of this is that any software using the OCI "odesc" function call will think that they are loading data into a number(10) column when in fact they are loading a float. The danger here is that if the data being loaded is greater than 3/4 digits it will be truncated but the oracle server will not raise an error or warning.

Peter Halnon.

Michel Cadot <micadot_at_altern.org> wrote in message news:7p9cah$h76$1_at_oceanite.cybercable.fr...
> The simple test in the web site below is :
> create table oracheck (val float(10));
> insert into oracheck (val) values (11111);
> select val from oracheck;
> VAL
> ----------
> 11110
>
> I'm not sure that it's a bug.
>
> Actually, float(10) specifies a floating point number with
> *binary* precision 10 that is a *decimal* precision of 3 or 4
> (conversion from binary to decimal precision is made by
> multiplying by 0.30103).
>
> In the test, 11111 has 5 decimal digits, so it is converted
> to the precision requested that is 11110.
>
>
> Peter Hanlon a écrit dans le message
> <934817277.9570.0.nnrp-06.9e98b813_at_news.demon.co.uk>...
> >ALERT: ORACLE 8 NUMERIC PROCESSING ERROR
> >=======================================
> >
> >During a recent benchmarking exercise Hanlon Consulting engineers
discovered
> >that floating point data was being corrupted during loads using
SQL*Loader.
> >Further investigation revealed that the corruption was taking place on
the
> >Oracle 8 server and not in the loader itself, meaning that ALL oracle
tools
> >and bespoke software will be affected by the problem.
> >
> >Affected Systems
> >================
> >
> >The bug affects database columns defined as "FLOAT" and is present under
> >8.0.5 and 8.1.5 of Oracle. The bug may also exist in earlier versions of
> >Oracle but these environments haven't been tested as yet. The platforms
that
> >have been tested are Solaris, Linux and HP-UX and they all behave in the
> >same way.
> >
> >A web page with the platforms and Oracle versions affected is available
at
> >http://www.hanlon.co.uk/oraclebug. This page also contains some more
> >information and a simple test that can be run against an Oracle instance
to
> >determine if your database is at risk. If you identify the problem please
> >e-mail enquire_at_hanlon.co.uk with your Oracle version and platform and we
> >will update the information on our page.
> >
> >Recommended action
> >==================
> >
> >We currently believe that any data that has already been inserted into a
> >float and corrupted will not be recoverable. A number of work-arounds are
> >currently being tested to determine if a fix can be developed. Any fixes
or
> >work-arounds will be made available from
http://www.hanlon.co.uk/oraclebug
> >as they are developed.
> >
> >--
> >Peter Hanlon.............................Email : pete_at_hanlon.co.uk
> >Hanlon Consulting Ltd.............Phone : +44 (0)20 8391 4021
> >http://www.hanlon.co.uk/ ........Fax : +44 (0)870 056 7283
> >
> >
>
>
Received on Sat Aug 21 1999 - 13:31:30 CEST

Original text of this message