Corrupted number scales for certain exponents · Issue #300 · oracle/python-cx_Oracle (original) (raw)

When inserting numbers between 1e-9 and 1e-7, the values stored in oracle do not have the correct scale:

Expected output:

[[ 0.                 1.               ]
 [ 1.                 0.1              ]
 [ 2.                 0.01             ]
 [ 3.                 0.001            ]
 [ 4.                 0.0001           ]
 [ 5.                 0.00001          ]
 [ 6.                 0.000001         ]
 [ 7.                 0.0000001        ]
 [ 8.                 0.00000001       ]
 [ 9.                 0.000000001      ]
 [10.                 0.0000000001     ]
 [11.                 0.00000000001    ]
 [12.                 0.000000000001   ]
 [13.                 0.0000000000001  ]
 [14.                 0.00000000000001 ]
 [15.                 0.000000000000001]]

Actual output from Oracle:

[[ 0.                 1.               ]
 [ 1.                 0.1              ]
 [ 2.                 0.01             ]
 [ 3.                 0.001            ]
 [ 4.                 0.0001           ]
 [ 5.                 0.00001          ]
 [ 6.                 0.000001         ]
 [ 7.                 0.0000001        ]
 [ 8.                 1.               ]  # ERROR: Should be 1e-8
 [ 9.                 1.               ]  # ERROR: Should be 1e-9
 [10.                 0.0000000001     ]
 [11.                 0.00000000001    ]
 [12.                 0.000000000001   ]
 [13.                 0.0000000000001  ]
 [14.                 0.00000000000001 ]
 [15.                 0.000000000000001]]

The following code replicates the problem:

import unittest

import cx_Oracle import numpy as np from numpy.testing import assert_allclose

class MyTestCase(unittest.TestCase): NUM_IDS = 16

def setUp(self):
    config = {
        'username': '<USER>',
        'password': '<PASSWORD',
        'host': '<HOST>',
        'port': 1521,
        'service_name': '<SERVICE NAME>'
    }

    self._database_url = '{username}/{password}@{host}:{port}/{service_name}'.format(**config)

    create_tbl = 'CREATE TABLE TEST (ID NUMBER, VALUE NUMBER)'

    test_values = [np.power(10.0, - x) for x in range(self.NUM_IDS)]
    self.test_data = list(zip(range(self.NUM_IDS), test_values))

    with cx_Oracle.connect(self._database_url) as conn, conn.cursor() as cursor:
        cursor.execute(create_tbl)
        cursor.executemany('INSERT INTO TEST (ID, VALUE) VALUES (:0, :1)', self.test_data)
        conn.commit()

def test_numbers(self):
    with cx_Oracle.connect(self._database_url) as conn, conn.cursor() as cursor:
        out = cursor.execute('select id, value from test order by id').fetchall()
        np.set_printoptions(precision=self.NUM_IDS, suppress=True)
        # INPUT
        print(np.array(self.test_data))
        # OUTPUT
        print(np.array(out))

        assert_allclose(out, self.test_data, rtol=0, atol=1e-14, verbose=True)

Environment: