Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 400 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
How to get the same value of NUMBER type in Oracle db when cursor.fetchall() is called

User_4YI8U
Member Posts: 2 Green Ribbon
in Python
Hi,
In my Oracle 19c database, I have a table with many columns of type NUMBER and they store the values like for example 100, 0, 250 etc. However, when SQL Query is executed using cx_Oracle python module (v8.2.1), the cursor.fetchall() method is returning the values of those NUMBER type columns as 100.0, 0.0, 250.0 (with single decimal point). As a result our automation test results comparison is failing.
Could you please suggest/guide me on how to get the exact value of those NUMBER type columns in the same way how Oracle db stored without that additional decimal value (dot zero). Please let me know if you need any additional information on the issue.
Regards,
Chandrasekhar
Answers
-
- as numbers, the value you quoted are the same.
- Python deals with binary representation of numbers, whereas Oracle has a decimal representation, so there are bound to be some cases where conversion follows rules that you won't like. You can get around this using Python Decimal objects via an output type handler, see https://cx-oracle.readthedocs.io/en/latest/user_guide/sql_execution.html#fetched-number-precision.
- you can also use an output type handler to fetch as strings instead of Decimal - this is probably what you think you want.
- A general recommendation is to do as much numeric calculation in Oracle DB as possible (using SQL or PL/SQL) to avoid conversion issues, particularly when doing financial calculations.
-
Ok. Thank you