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

[D.R. B. <da...@as...>]

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