[DB-SIG] Result from DCOracle2 different from sqlplus

[Magnus Lyckå]

At 23:39 2001-11-14 -0800, Kim Titu wrote:
>Hi,
>   I am confused when retrieve data from database using
>same query. For instance, my query is "SELECT emp_id
>FROM EMPLOYEE" and data type of emp_id is NUMBER. If i
>execute this query using DCOracle2 in python, i get id
>number with 1 decimal number like, [[1.0],[14.0]].
>However, if i run the same query in sqlplus, i get the
>same result without any decimal point. Why can this
>happen? Any idea?

Python has four numeric types: integer, long, float
and complex. I think we can skip complex for now though.

Long and integer can't display any fractions, just whole
numbers. Long can hold any size of whole number, but
integer is limited by the CPU, for instance to 32 bits.

Float can handle fractions and very large and small
numbers, but only with a limited precision. They are
stored in the computer in a binary format called
IEEE 754 double precision. See for instance
http://research.microsoft.com/~hollasch/cgindex/coding/ieeefloat.html
(To be picky, they use the C type double of the C compiler
used to compile Python, but they typically use this standard.)
These numbers can span a large range of values, but they
can't store all decimal values exactly.

For example:
 >>> x = 0.6
 >>> x
0.59999999999999998
but
 >>> print x
0.6
(Python is rounding a bit here.)

Oracle's NUMBER is another beast. It's not a floating
point number, but rather a fixed point number where
you can define a range for its size. This means that
you can use it for fractions, but still know the exact
value.

So, while Python gives you a choice between exact
integers or inexact numbers with fractions, you can
actually get both in Oracle's NUMBER type.

Since the NUMBER type *can* hold fractions, there is
in the general case no choice but to map it to float
in Python. Python  displays all floats with at least
one decimal, regardless of their values. Otherwise you
wouldn't be able to see if it was a float or an integer.

 >>> print float(1)
1.0

Of course, in many cases NUMBER values could be mapped
to Python integer or long, but this depends on their
values. It would be stupid to implement a DB-API so that
you never know what type to expect from a NUMBER column
in a database. Right?

But NUMBER takes two arguments, precision and scale.
For instance NUMBER(5,2) can store the values: -999.99,
-999.98 ... +999.98, +999.99. Just typing NUMBER(5)
is short for NUMBER(5,0), and just typing NUMBER is
short for NUMBER(38). (At least in Oracle 7).

For instance NUMBER(9,0), could be mapped to
integer, but NUMBER(10,0) would need to be mapped to
long, and of course NUMBER(5,2) would need to be a
float.

DCOracle takes the more convenient path and maps all
NUMBER to float. If you want integers, you can use the
INTEGER data type in your SQL.

This is also a safe choice. I don't know how Oracle NUMBER
is implemented, but the SQL standard data type DECIMAL does
actually allow the database to store more than the
declaration of the data type suggests. For instance it is
legal for a database to allow a larger value than 9.9 to be
stored in a field declared as DECIMAL(2,1), but it is illegal
if it's declared as NUMERIC(2,1). If Oracle's NUMBER behaves
like SQL DECIMAL, mapping NUMBER(9) to integer might lead
to overflows.

Please note that NUMBER is not an SQL type, but an Oracle
proprietary type. If you have a recent enough Oracle I would
suggest that you use one of the standard SQL numeric data
types. (I know Oracle 7 didn't have any other numeric type.)

In your case I imagine the employee ID is really an INTEGER.
There is really no reason to use a datatype allowing for
fractions and values larger than 2147483647 to use number
like 1 and 14.

It could actually be practical with a fixed point decimal data
type or standard class in Python. These kinds of values are common
in relational databases and for instance in administrative systems.
I think COBOL has such a data type. Unfortunately, this does not
exist in Python or in the DB-API standard.

Understanding the limitations of various numeric types and the
consequences of using them is important for programmers.
Particularly the lack of precision in floats. For instance,
you should never compare floats for equality, but rather use
something like:

def equal(a,b,fuzzFactor):
     if a-fuzzFactor < b < a+fuzzFactor:
         return 1
     else:
         return 0


--
Magnus Lyckå, Thinkware AB
Älvans väg 99, SE-907 50 UMEÅ
tel 070-582 80 65, fax: 070-612 80 65
http://www.thinkware.se/  mailto:magnus@thinkware.se