Re: [cx-oracle-users] Fetching NaN, -Infinity and +Infinity from BINARY_FLOAT or BINARY_DOUBLE colu

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

Good morning Anthony,

Below you'll find a - hopefully - self-contained example to reproduce
the errors that can be caused by fetching NaN and Infinity as "binary"
doubles and floats.
I have the impression that this cannot be solved with a just few lines of
C code, but you are the expert here. :)

Ciao,

                                                   Danny

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
import struct, sys
import cx_Oracle

connectstring = 'user/password@tnsname'

# Clumsy way to obtain Infinity and NaN as float and double
floatinf,  = struct.unpack('f', struct.pack('L', 0x7F800000))
floatnan,  = struct.unpack('f', struct.pack('L', 0x7F800001))
doubleinf, = struct.unpack('d', struct.pack('Q', 0x7FF0000000000000L))
doublenan, = struct.unpack('d', struct.pack('Q', 0x7FF0000000000001L))

assert str(floatinf) == 'inf', 'floatinf is not "inf", but %s' % floatinf
assert str(floatnan) == 'nan', 'floatnan is not "nan", but %s' % floatnan
assert str(doubleinf) == 'inf', 'doubleinf is not "inf", but %s' % doubleinf
assert str(doublenan) == 'nan', 'doublenan is not "inf", but %s' % doublenan

# Get Infinity and NaN as constants to trigger exceptions
connection = cx_Oracle.connect(connectstring)
cursor = connection.cursor()

for typeconversion in ['TO_BINARY_DOUBLE', 'TO_BINARY_FLOAT']:
    for number in ['floatinf', 'floatnan', 'doubleinf', 'doublenan']:
        print 'Trying %(typeconversion)s for %(number)s' % vars()
        cursor.execute('SELECT %(typeconversion)s(:1) FROM DUAL' % vars(), [eval(number)])
        try:
            cursor.fetchone()
            print 'Ok'
        except cx_Oracle.DatabaseError, e:
            print e

cursor.close()
connection.close()

# Expected output
# Trying TO_BINARY_DOUBLE for floatinf
# OCI-22065: number to text translation for the given format causes overflow
# 
# Trying TO_BINARY_DOUBLE for floatnan
# ORA-01722: invalid number
# 
# Trying TO_BINARY_DOUBLE for doubleinf
# OCI-22065: number to text translation for the given format causes overflow
# 
# Trying TO_BINARY_DOUBLE for doublenan
# ORA-01722: invalid number
# 
# Trying TO_BINARY_FLOAT for floatinf
# OCI-22065: number to text translation for the given format causes overflow
# 
# Trying TO_BINARY_FLOAT for floatnan
# ORA-01722: invalid number
# 
# Trying TO_BINARY_FLOAT for doubleinf
# OCI-22065: number to text translation for the given format causes overflow
# 
# Trying TO_BINARY_FLOAT for doublenan
# ORA-01722: invalid number
--------------------------------------------------------------------------------

On Wed, Jan 31, 2007 at 04:31:21PM -0700, Anthony Tuininga wrote:
> I'm assuming the attached script yields these errors but unfortunately
> I don't know which incantation is necessary (which parameters to pass)
> in order to get the associated error. Can you enlighten me? Thank you.
> 
> On 1/12/07, D.R.   Boxhoorn <da...@as...> wrote:
> >
> >When fetching a NaN number (nan in Python) from a BINARY_FLOAT or 
> >BINARY_DOUBLE
> >column, the following exception is raised.
> >
> >   Traceback (most recent call last):
> >     File "<stdin>", line 1, in ?
> >   cx_Oracle.DatabaseError: ORA-01722: invalid number
> >
> >Similary, fetching Infinity (inf in Python) from a BINARY_FLOAT or 
> >BINARY_DOUBLE
> >column results in
> >
> >   Traceback (most recent call last):
> >     File "<stdin>", line 1, in ?
> >   cx_Oracle.DatabaseError: OCI-22065: number to text translation for the 
> >   given format causes overflow
> >
> >Inserting Infinity and NaN, however, works flawlessly.
> >
> >
> >I've only tried this for cx_Oracle 4.2.1 & 4.1.2, Python 2.3 and Oracle 
> >10gR2,
> >but it would not be surprising if other combinations show the same 
> >behaviour.
> >
> >Does anyone have an idea for a solution or clever workaround?
> >
> >Thanks
> >
> >                                                   Danny
> >
> >
> >Using cx_Oracle 4.2.1
> >On Thu, Dec 07, 2006 at 09:30:17AM +0100, Henning von Bargen wrote:
> >> Hi Mark,
> >>
> >> pls try the following script:
> >> =============================================
> >>
> >> #!/bin/env python
> >> # -*- coding: iso-8859-1 -*-
> >> #
> >> import cx_Oracle
> >>
> >> # Sample 1
> >> def doAllCurs (curs, stmt, **args):
> >>     rset = curs.execute (stmt, **args)
> >>     for rec in curs:
> >>         yield rec
> >>     return
> >>
> >> # Sample 2
> >> def doAllConn (conn, stmt, **args):
> >>     curs = conn.cursor()
> >>     for rec in doAllCurs(curs, stmt, **args):
> >>         yield rec
> >>     curs.close()
> >>     return
> >>
> >> # Print All Records
> >> # This could  perhaps be recoded in a more functional programming style
> >> def printAll (curs, stmt, **args):
> >>     for rec in doAllCurs(curs, stmt, **args):
> >>         print rec
> >>
> >> def test (uid, query, **bindvars):
> >>     conn = cx_Oracle.connect(uid)
> >>     curs = conn.cursor()
> >>     printAll (curs, query, **bindvars)
> >>
> >>     print ""
> >>     print "The first 20 records as a list:"
> >>     print list (doAllCurs (curs, query, **bindvars))[:20]
> >>
> >>     curs.close()
> >>     conn.close()
> >>
> >> # Test me
> >> if __name__ == "__main__":
> >>     import sys
> >>     if len(sys.argv) > 1:
> >>         uid = sys.argv[1]
> >>         conditions = dict ([tuple(arg.split("=",1))
> >>                             for arg in sys.argv[2:]
> >>                            ])
> >>         query = """
> >> select object_name
> >>      , object_type
> >>   from user_objects
> >>  where 1=1"""
> >>         for nam,val in conditions.items():
> >>             query += ("\n and %s = :%s" % (nam,nam))
> >>         print "Query:"
> >>         print query
> >>         print "*" * 70
> >>         print "Bind Vars:"
> >>         for nam,val in conditions.items():
> >>             print "%-20s = %r" % (nam,val)
> >>         print "*" * 70
> >>         test (uid,  query, **conditions);
> >>     else:
> >>         print "Test me like this:"
> >>         print "%s uid/pwd@dbs object_type=TABLE" % sys.argv[0]
> >>
> >> =============================================
> >>
> >> Just save it as test.py, then call it like
> >> test.py userid/password@database object_type=TABLE
> >>
> >> or, with other condiditons:
> >> test.py userid/password@database object_type=TABLE object_name=XYZ
> >> (This will probably not return any results)
> >>
> >> HTH
> >> Henning
> >>
> >> -------------------------------------------------------------------------
> >> Take Surveys. Earn Cash. Influence the Future of IT
> >> Join SourceForge.net's Techsay panel and you'll get the chance to share 
> >your
> >> opinions on IT & business topics through brief surveys - and earn cash
> >> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> >> _______________________________________________
> >> cx-oracle-users mailing list
> >> cx-...@li...
> >> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >-------------------------------------------------------------------------
> >Take Surveys. Earn Cash. Influence the Future of IT
> >Join SourceForge.net's Techsay panel and you'll get the chance to share 
> >your
> >opinions on IT & business topics through brief surveys - and earn cash
> >http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> >_______________________________________________
> >cx-oracle-users mailing list
> >cx-...@li...
> >https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >

Re: [cx-oracle-users] Fetching NaN, -Infinity and +Infinity from BINARY_FLOAT or BINARY_DOUBLE colu

Re: [cx-oracle-users] Fetching NaN, -Infinity and +Infinity from BINARY_FLOAT or BINARY_DOUBLE columns