4 Replies Latest reply: May 21, 2013 6:27 AM by user8803644 RSS

    cx_Oracle.NUMBER precision problem ..

    user8803644
      Hi All,

      i understand that floating point numbers are inherently inaccurate, eg:
      0.1 + 0.2 != 0.3

      the problem i'm seeing is that:

      in sqlplus, a query is returning 0.088 from the Oracle db server,
      but cx_Oracle is changing the value to 0.08800000000000001.

      at the interpretor, v = 0.088, retains 0.088...
      (most likely, the precision is maintained upon definition),
      is there a way to set the precision for cx_Oracle.NUMBER ?

      thanks very much in advance!

      sam
        • 1. Re: cx_Oracle.NUMBER precision problem ..
          cj
          Since I don't know what you are trying to do, or how you coded it, here's once reference: glance at the input/output handler section in http://www.oracle.com/technetwork/articles/tuininga-cx-oracle-084866.html
          • 2. Re: cx_Oracle.NUMBER precision problem ..
            user8803644
            Thanks CJ,

            i have seen the document you referenced.

            this is what i'm trying to do:

            <h6>
            SQL> select volume from goods;
            
            VOLUME
            ------
            0.088
            
            1 row selected
            
            (python/cx_Oracle)
            
            cur.execute("select volume from goods")
            rows = cur.fetchall()
            rows
            [(0.08800000000000001,)]
            
            v = 0.088
            v
            0.088
            </h6>

            the value stored in the DB has been set to precision 0.3f, and
            sqlplus returns it as-is.

            in python/cx_Oracle, the value is 0.08800000000000001.

            the example shows what is typed at the interpreter, and also
            in python scripts (outside of cx_Oracle).

            assignments keep the precision specified.

            is there a way to get the same behaviour ?

            thanks,
            sam
            • 3. Re: cx_Oracle.NUMBER precision problem ..
              cj
              What about using one of these:
              # t.py
              
              import cx_Oracle
              import decimal
              
              db = cx_Oracle.connect("cj", "cj", "localhost/orcl")
              
              cur = db.cursor()
              cur.execute("select volume from goods")
              rows = cur.fetchall()
              print rows
              
              cur = db.cursor()
              cur.execute("select to_char(volume) from goods")
              rows = cur.fetchall()
              print rows
              print float(rows[0][0])
              
              def NumbersAsDecimal(cursor, name, defaultType, size, precision, scale): 
                  if defaultType == cx_Oracle.NUMBER: 
                      return cursor.var(str, 100, cursor.arraysize, outconverter = decimal.Decimal) 
              
              db.outputtypehandler = NumbersAsDecimal
              cur = db.cursor()
              cur.execute("select volume from goods")
              rows = cur.fetchall()
              print rows
              Output is:
              $ python t.py 
              [(0.08800000000000001,)]
              [('.088',)]
              0.088
              [(Decimal('0.088'),)]
              • 4. Re: cx_Oracle.NUMBER precision problem ..
                user8803644
                hi CJ,

                thank you very much for your suggestions!

                i cannot use the to_char/string solutions,
                because we need to be able to sort the data
                later.

                i used the decimal solution you proposed,
                and that resolved the problem.

                the next problem i ran into, was that
                json was not able to serialize decimal types.

                came across the following at:
                http://djangosnippets.org/snippets/2410/

                which help with the floats ...

                (sorry for the lapse and lateness in reply,
                was testing and adapting the solution.. and
                then worked on another issue)

                Thanks again! you've been a great help!
                sam