Strange Oracle error: "invalid text format"

Question

I am trying to get some data from a column, DATA_TYPE = NUMBER (1,0) with this piece of code:

import cx_Oracle conn = cx_Oracle.connect(usr, pwd, url) cursor = conn.cursor() cursor.execute("SELECT DELETED FROM SERVICEORDER WHERE ORDERID='TEST'") print(cursor.fetchone()[0]) 

who complains in this way:

 Traceback (most recent call last): File "main.py", line 247, in <module> check = completed() File "main.py", line 57, in completed deleted = cursor.fetchone()[0] cx_Oracle.DatabaseError: OCI-22061: invalid format text [T 

Replacing the “DELETED” column with those whose DATA_TYPE = VARCHAR2 does not cause such a complaint.

Answer 1

A workaround puts time.sleep(1) before cursor.fetchone() :

 ... cursor.execute("SELECT DELETED FROM SERVICEORDER WHERE ORDERID='TEST'") time.sleep(1) print(cursor.fetchone()[0]) 

Answer 2

Now I ran into this problem using cx_Oracle 5.0.4 with Unicode support. The above solution did not help me. The DELETED column in the question is a numeric column, which causes this error.

According to the mailing list ( http://comments.gmane.org/gmane.comp.python.db.cx-oracle/2390 ), this could be an error in Oracle that only appears in cx_Oracle with Unicode support.

link: "When I create cx_Oracle without Unicode support, everything works as expected. When I create cx_Oracle with Unicode support, I try to use a query that returns a numeric value (for example):

 con = Connection( ... ) cur = con.cursor() cur.execute( 'SELECT 1 FROM DUAL' ) rows = cur.fetchall() 

leads to this exception:

 cx_Oracle.DatabaseError: OCI-22061: invalid format text [T 

"

What I did to get around this is in the select, do statement:

 cur.execute( 'SELECT to_char(1) FROM DUAL' ) rows = cur.fetchall() for row in rows: val = int(row[0]) 

This is pretty ugly, but it works.

Answer 3

These types of errors went away when I upgraded to cx_Oracle 5.1. If the RPM does not install (as it happened for me in Red Hat 5.5), you can usually rpm2cpio the file, take cx_Oracle.so and put it in the python site-packages directory.

Answer 4

I had the same error.

Commit helped me:

 conn = cx_Oracle.connect(...) ... cursor.execute() conn.commit()