While extracting a value from Oracle (using Python) it's precision is getting lost-CodePudding

I am looking to load a high-precision value from Oracle to Snowflake. I am doing this by first extracting the value from the oracle in a CSV file through a Python Script and then Loading it in Snowflake DB. What I am finding is that the value is not getting extracted the same as the value in oracle. I see some issues with the precision. I am not sure if this is something to do with the issue in Oracle (Version 19.12.0.0.0) or the Python Script (V 3.7). Note that this behavior is for a large set of values and the above is the design I need to follow for loading.

Value in Oracle DB: 86525.200000000006. This is available in a column of data type ‘number(28,12)’

Value in the extracted CSV File: 86525.2

What I have tried already:

#1. Converted the number to Char as below:

select value1 ,cast(value1 as varchar2(1000))

from table1

here the Value1 is coming as ‘86525.2’

#2.

TO_CHAR(value1 ,'fm9999999999999999.999999999999'),

here the Value is coming as ‘86525.2’

#3. TO_CHAR(value1 ,'fm9999999999999999.000000000000'),

here the Value is coming as ‘86525.200000000000’

#4. (SUBSTR (value1 , INSTR (value1 , '.',1))) mydecimal,

here the Value is coming as ‘86525.2’

#5. to_number(regexp_replace(to_char(value1), '^[0-9] .', '')) rexp,

here the Value is coming as ‘86525.2’

Any help will be highly appreciated. Thank you.

CodePudding user response：

Value is correctly stored into the table:

Sample data:

SQL> create table test (val number(28, 12));

Table created.

SQL> insert into test values (86525.200000000006);

1 row created.

Contents:

SQL> select * From test;

       VAL
----------
   86525,2             --> not all decimals here

Set column format:

SQL> col val format 99990d000000000000
SQL> select * From test;

                VAL
-------------------
 86525,200000000006             --> correct!

SQL>

to_char works as well:

SQL> select to_char(val, '99990d000000000000') result from test;

RESULT
-------------------
 86525,200000000006

SQL>

CodePudding user response：

The problem is that you use the default data type for NUMBER which is the Python float, that is responsible for the precision loast.

You must switch to the use of the Decimaldata type which has a better precision.

As documented you must define an output type handler that makes the conversion to Decimal in the cursor.

Example

The table from other answer is used.

def number_to_decimal(cursor, name, default_type, size, precision, scale):
    if default_type == cx_Oracle.DB_TYPE_NUMBER:
        return cursor.var(decimal.Decimal, arraysize=cursor.arraysize)

cursor = connection.cursor()
cursor.outputtypehandler = number_to_decimal

cursor.execute("select val from test" )

for val in cursor:
    print("Type  :", type(val[0]))
    print("Values:", val[0])

Output

Type  : <class 'decimal.Decimal'>
Values: 86525.200000000006