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

cx_Oracle 6.0.1 Seems to Retrieve Wrong Values for Large Numbers (> 10 digits) #70

Closed
CassonStallings opened this Issue Aug 22, 2017 · 5 comments

Comments

Projects
None yet
3 participants
@CassonStallings
Copy link

CassonStallings commented Aug 22, 2017

I am using cx_Oracle to query a table with a 10 digit and larger numbers used as keys. When pulled into Python they are being somehow corrupted. The original database column is defined as Number(16). In the test below 9 digit and some 10 digit numbers will be correctly queried. Numbers with 11 or more digits are not correctly queried. The 10 digit number 1234567890 is correctly queried, the larger number 2234567890 is not. I have checked in the database and the numbers are being correctly inserted.

For the time being I am converting the number to_char.

I've tried to make the example similar to a test case, although I'm not exactly sure what you need for that.

import sys
import cx_Oracle
print('Python:', sys.version)
print('cx_Oracle:', cx_Oracle.version)
print('Oracle Client:', cx_Oracle.clientversion())
connection = cx_Oracle.connect(CONN_STR)
connection.autocommit = True
cursor = connection.cursor()
cursor.execute('CREATE TABLE DS_TEST(DATA NUMBER(16))')

numbers = [123456789, 1234567890, 2234567890, 123456789012, 1234567890123, 1234567890123456]
for n in numbers:
    cursor.execute('INSERT INTO DS_TEST(DATA) VALUES (' + str(n) + ')')
for n in numbers:
    cursor.execute('SELECT * FROM DS_TEST WHERE DATA='+str(n))
    row = cursor.fetchone()
    assert row is not None
    print('Result:', row[0])
    assert row[0] == n

The results are:

>>> Python: 3.6.1 |Anaconda custom (64-bit)| (default, Mar 22 2017, 20:11:04) [MSC v.1900 64 bit (AMD64)]
>>>cx_Oracle: 6.0.1
>>>Oracle Client: (11, 2, 0, 3, 0)
>>>Result: 123456789
>>>Result: 1234567890
>>>Result: -2060399406
>>> ---------------------------------------------------------------------------
>>>AssertionError                            Traceback (most recent call last)
>>><ipython-input-65-354130a0893c> in <module>()
>>> 17     assert row is not None
>>> 18     print('Result:', row[0])
>>>---> 19     assert row[0] == n

AssertionError:

Answer the following questions:

  1. What is your version of Python? Is it 32-bit or 64-bit? Python 3.6.1

  2. What is your version of cx_Oracle? 6.0.1

  3. What is your version of the Oracle client (e.g. Instant Client)? How was it
    installed? Where is it installed? 11.2

  4. What is your version of the Oracle Database? 11.2

  5. What is your OS and version? Windows 7 Pro SP 1
    I am running in IPython if that makes a difference.

  6. What compiler version did you use? For example, with GCC, run
    gcc --version. Unknown

  7. What environment variables did you set? How exactly did you set them? Unknown

  8. What exact command caused the problem (e.g. what command did you try to
    install with)? Who were you logged in as? See example

  9. What error(s) you are seeing? See description

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Aug 22, 2017

I can replicate the issue on Windows....but not on Linux. Will look into it further and add a test case once the source of the issue has been determined. Thanks for reporting it!

anthony-tuininga added a commit that referenced this issue Aug 23, 2017

On Windows, sizeof(long) = 4 which means that integers between 10 and…
… 18 digits

were not converted to Python correctly
(#70).
@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Aug 23, 2017

The problem was due to sizeof(long) = 4 on Windows and a lack of a test case -- both have now been resolved!

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented Aug 30, 2017

The correction for this issue was included in 6.0.2, which was just released.

@GuiuGaldino

This comment has been minimized.

Copy link

GuiuGaldino commented May 16, 2018

I'm using version 6.2.1 and looks like this problem still exists.

Answer some questions:
Answer the following questions:
What is your version of Python? Is it 32-bit or 64-bit? Python 3.6

What is your version of cx_Oracle? 6.2.1

What is your version of the Oracle client (e.g. Instant Client)? How was it
installed? Where is it installed? 11.2

What is your OS and version? Windows 10 Pro

Thank you.

@anthony-tuininga

This comment has been minimized.

Copy link
Member

anthony-tuininga commented May 16, 2018

I just tried the code at the top of this issue and it still works correctly for me. If you can find a way to demonstrate the problem, please open a new issue with the code that causes the problem. Thanks!

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