Re: [cx-oracle-users] Fetching NaN, -Infinity and +Infinity from BINARY_FLOAT or BINARY_DOUBLE colu
Brought to you by:
atuining
From: Anthony T. <ant...@gm...> - 2007-02-03 19:23:20
|
Thank you for the simple test case. :-) I've managed to now implement this so that when native doubles or floats are returned from an Oracle 10g database they are automatically returned as a native float or double without any unnecessary conversion process. I have __NOT__ done the same for binding, however, because of the fact that you cannot use those types when using an Oracle 10g client against an Oracle 9i server. Did you want a patch for this or do you want to wait until I make the next release -- hopefully in the next couple of weeks. On 2/1/07, D.R. Boxhoorn <da...@as...> wrote: > > 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 > > > > |