FW: FW: bitand functions and NUMBER(20)

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 19 Jul 2007 14:23:59 -0400
Message-ID: <023d01c7ca31$fa936380$1100a8c0@rsiz.com>

It starts giving bad results somewhere between 9199999999999999999 and
9299999999999999999.

SQL> c/93/92

  1* select bitand(9999999999999999999,9299999999999999999) value from dual

SQL> r

  1* select bitand(9999999999999999999,9299999999999999999) value from dual

                VALUE


---------------------

 09223372036854775807 <---- wrong, and apparently anything with an internal calculation result set at least this big comes back with this answer. I didn't truss this. I'll be happily enlightened if someone chimes in with a better analysis of this or where I'm going wrong with the thought process, but I can't put in any more time right now.

SQL> c/92/91

  1* select bitand(9999999999999999999,9199999999999999999) value from dual

SQL> r

  1* select bitand(9999999999999999999,9199999999999999999) value from dual

                VALUE


---------------------

 09199999999999999999    <---- correct

-----Original Message-----

From: Mark W. Farnham [mailto:mwf_at_rsiz.com] Sent: Thursday, July 19, 2007 2:03 PM
To: 'Stefan Kuhn'
Subject: RE: FW: bitand functions and NUMBER(20)

I'm getting the same results as you with all the to_numbers supplied.

This is also interesting: (That 9223372036854775807 value is clearly some internal overflow maximum). I think you've found a bug unless I'm missing something.

  1* select 19999999999999999999+1 value from dual

                VALUE


---------------------

 20000000000000000000

(so the display and whatever clearly has enough width)

SQL> r

  1* select bitand(1999999999999999999,1999999999999999999) value from dual

                VALUE


---------------------

 01999999999999999999

(19 digits works just fine)

SQL> c/9,/99,

  1* select bitand(19999999999999999999,1999999999999999999) value from dual

SQL> c/9)/99)

  1* select bitand(19999999999999999999,19999999999999999999) value from

dual
SQL> r

  1* select bitand(19999999999999999999,19999999999999999999) value from

dual

                VALUE


---------------------

 09223372036854775807

(twenty digits gets this answer.)

So other than Oracle fixing the bug or you using smaller values (two columns or something), you would have to use string manipulation of the character value of the number and carve it up for the bitand and concatenate the results.

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jul 19 2007 - 13:23:59 CDT