Insertion of Nan in Oracle numbers produces corrupt data · Issue #91 · oracle/python-cx_Oracle (original) (raw)

It is possible to insert nan values into Oracle numbers.

#!/usr/bin/env python
from numpy import nan
import cx_Oracle

connection = cx_Oracle.connect('/@TSTRP0')
cur = connection.cursor()

cur.execute( 'drop table ieee754' );
cur.execute( 'create table ieee754( id varchar2(10), oracle_number number, native_float binary_double )' )
cur.execute( "insert into ieee754(id,oracle_number,native_float) values ( 'Nan', :1, :2 )", ( nan, nan ))

connection.commit()

This produces the following data in the database:

SCOTT@TSTRP0> set null <NULL>
SCOTT@TSTRP0> select id, ORACLE_NUMBER, dump(oracle_number,16) xON, native_float, dump(native_float,16) xBD from ieee754;

ID			  ORACLE_NUMBER XON			  NATIVE_FLOAT XBD
------------------------- ------------- ------------------------- ------------ ---------------------------------------------
Nan					Typ=2 Len=1: c1 		   Nan Typ=101 Len=8: ff,f8,0,0,0,0,0,0

0xC1 is not an Oracle number:

SCOTT@TSTRP0> select to_char( ORACLE_NUMBER ) from ieee754;
select to_char( ORACLE_NUMBER ) from ieee754
                *
ERROR at line 1:
ORA-01722: invalid number

Its use may cause crashes, CPU spin or wrong results.

Answer the following questions:

What is your version of Python? Is it 32-bit or 64-bit?

Python 2.7.6 (default, Feb 12 2015, 15:58:12) 
[GCC 4.4.6 20120305 (Red Hat 4.4.6-4)] on linux2

64 bits.
2. What is your version of cx_Oracle?
Above test was done in 5.0.3, but I've reproduced with 5.1 and 5.2

What is your version of the Oracle client (e.g. Instant Client)? How was it
installed? Where is it installed?
11.2.0.4; was installed with
What is your version of the Oracle Database?
Reproduced in 11.2.0.4 and 12.1.0.2
What is your OS and version?

Linux myvm 2.6.32-279.22.1.el6.x86_64 #1 SMP Sun Jan 13 09:21:40 EST 2013 x86_64 x86_64 x86_64 GNU/Linux

Client
6. What compiler version did you use? For example, with GCC, run
gcc --version.
GCC 4.4.6 20120305 (Red Hat 4.4.6-4)
7. What environment variables did you set? How exactly did you set them?
Not relevant here
8. What exact command caused the problem (e.g. what command did you try to
install with)? Who were you logged in as?
See above
9. What error(s) you are seeing?
None during the insertion of the Nans, but my users do operations with them and this has produced core dumps from the shadow processes (or worse, shared servers), crashed the database once or twice and produced wrong results.