New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
Bulva opened this Issue Mar 20, 2018 · 2 comments

Comments

Projects
None yet
2 participants
@Bulva
Copy link

Bulva commented Mar 20, 2018

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

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Mar 20, 2018

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 added a commit to oracle/odpi that referenced this issue Mar 21, 2018

Correct the handling of ANSI types REAL and DOUBLE PRECISION as imple…
…mented by

Oracle. These types are just subtypes of NUMBER and are not actually stored as
native floating point numbers. Native floating point numbers are used with
Oracle types BINARY_FLOAT and BINARY_DOUBLE
(oracle/python-cx_Oracle#163).

anthony-tuininga added a commit that referenced this issue Mar 21, 2018

Correct the handling of ANSI types REAL and DOUBLE PRECISION as imple…
…mented by

Oracle. These types are just subtypes of NUMBER and are not actually stored as
native floating point numbers. Native floating point numbers are used with
Oracle types BINARY_FLOAT and BINARY_DOUBLE
(#163).
@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Mar 21, 2018

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment