search

oracle / python-cx_Oracle

Python interface to Oracle Database now superseded by python-oracledb
https://oracle.github.io/python-cx_Oracle
Other
888 stars 361 forks source link

Non-printable (randomly changing) character inserted for -0.0 instead of inserting 0 #274

Closed mauropagano closed 5 years ago

mauropagano commented 5 years ago

Answer the following questions:

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

  2. What is your cx_Oracle version? -> 7.1.1

  3. What error(s) you are seeing? -> No error raised by cx_Oracle

  4. What OS (and version) is Python executing on? -> Linux and MacOS

  5. What is your version of the Oracle client (e.g. Instant Client)? How was it installed? Where is it installed? 12.2.0.1, both as instant client in MacOS or full client install in Linux

  6. What is your Oracle Database version? 12.2.0.1

  7. What is the PATH environment variable (on Windows) or LD_LIBRARY_PATH (on Linux) set to? On macOS, what is in ~/lib? -> to Oracle client lib

  8. What Oracle environment variables did you set? How exactly did you set them? -> "classic ones" (ORACLE_HOME, ORACLE_SID, LD_LIBRARY_PATH and PATH)

  9. Do you have a small, single Python script that immediately runs to show us the problem? -> Yes, testcase below

In database
SQL> create table test_cx (n1 number);
In Python
db = cx_Oracle.connect(....)
cursor = db.cursor()
cursor.execute("insert into test_cx values (:1)", (-0.0,))
db.commit()

and in database you'll see a non printable character that even changes value internally if you execute the insert multiple times (especially across sessions)

select n1, dump(n1, 16) mycol from test_cx;

    N1 MYCOL
---------- ------------------------------
       Typ=2 Len=2: 80,0
       Typ=2 Len=2: 80,0
2.200E-129 Typ=2 Len=2: 80,17
       Typ=2 Len=2: 80,93

the problem seems to be related to the way the value is bind-ed, passing the whole insert as string or just inserting the row from SQL*Plus the value is correctly stored as "0". No difference if target table has a NUMBER or FLOAT column, same problem. Also using executemany() passing a list with a single element exposes the same problem.

anthony-tuininga commented 5 years ago

Looks intriguing. I'll look into it and get back to you on this. What version of Python are you using?

mauropagano commented 5 years ago

Reproduces in both 3.5 and 3.7(.0)

anthony-tuininga commented 5 years ago

Excellent. Thanks.

anthony-tuininga commented 5 years ago

I have uncovered the source of the issue. This will be corrected in ODPI-C where this wasn't being handled properly.

anthony-tuininga commented 5 years ago

This was addressed in cx_Oracle 7.1.2 which was just released.