Wrong values returned from user defined type for DOUBLE PRECISION Oracle type

[Bulva]

Hi,

we are using cx_Oracle for export data from Oracle database. It is working pretty good but today we have a problem with wrong float values returning from our user defined type. We have these two types:

CREATE OR REPLACE TYPE geomext AS OBJECT (
   x               DOUBLE PRECISION,
   y               DOUBLE PRECISION,
   rotation        DOUBLE PRECISION,
   deg_start       DOUBLE PRECISION,   
   deg_sweep       DOUBLE PRECISION,   
   prim_axis       DOUBLE PRECISION,
   sec_axis        DOUBLE PRECISION, 
   width           DOUBLE PRECISION,
   height          DOUBLE PRECISION,
   justification   INTEGER,
   font            VARCHAR2 (255 CHAR),
   text            VARCHAR2 (1000 CHAR)
);

CREATE OR REPLACE TYPE geomext_array AS
  VARRAY (10000) OF geomext;

In SQL developer we are able to get correct values. The output from SQL developer for geomext object:
GEOMEXT(0,0,0.314937,0,0,0,0,0,0,2,NULL,'01/2000')

Here is how we get values via cx_Oracle:

# Reading object values with this method
def ObjectRepr(obj):
    if obj.type.iscollection:
        returnValue = []
        for value in obj.aslist():
            if isinstance(value, cx_oracle.Object):
                value = ObjectRepr(value)
            returnValue.append(value)
    else:
        returnValue = {}
        for attr in obj.type.attributes:
            value = getattr(obj, attr.name)
            if value is None:
                returnValue[attr.name] = 'NULL'
            elif isinstance(value, cx_oracle.Object):
                value = ObjectRepr(value)
            returnValue[attr.name] = value
    return returnValue

dsn = cx_oracle.makedsn(or_host, or_port, or_sid)
conn = cx_oracle.connect(user=or_user, password=or_password, dsn=dsn)
cursor = conn.cursor()
data = cursor.execute("SELECT id,geomext FROM geom_table").fetchall()

for insert in data:
    for col in insert:
        if isinstance(col, cx_oracle.Object):
            values = ObjectRepr(col)
            print(values)

But print statement for 0 value returns 1.619e-319 and for 0.314937 returns 8.10513527767e-313. Both are <class 'float'> types.

Python 3.6.2 64-bit
cx_Oracle - version 6.1.2 - upgraded from pip today
Oracle client - version 12.1.0.2.0
Oracle Database 12c Enterprise Edition
Windows 7 - 64-bit

[anthony-tuininga]

I can replicate this issue. It would appear to be due to the fact that your are using types that Oracle translates -- but not to what you expect! If you describe your type in SQL*Plus you get FLOAT(126), not BINARY_DOUBLE, which is I believe what you are hoping to get. Internally Oracle uses the NUMBER type so it will be restricted in scale and precision in different ways than BINARY_DOUBLE (double precision floating point numbers). For some reason OCI is still reporting this as BINARY_DOUBLE, however, which is why it isn't being converted properly. I'll look into this further and get back to you.

In the meantime, if you can change the type of your attribute to BINARY_DOUBLE instead you shouldn't run into this issue.

[anthony-tuininga]

It turns out that the ANSI types REAL and DOUBLE PRECISION are actually stored by Oracle as the NUMBER type. In fact, they are simply subtypes of the NUMBER type. I have adjusted the code to take this into account. If you were hoping for actual native floating point numbers you'll need to use the types BINARY_FLOAT and BINARY_DOUBLE instead.