[cx-oracle-users] OCI-22061: invalid format text

[Rob D. <Rob...@5o...>]

Hi

If anyone could help

OS                               -           CentOS release 5.4 (Final)
OS Version Type           -           (64Bit)
Cx_Oracle Version         -           CentOS 5 x86_64 RPM (Oracle 10g, Python 2.4)
Python                          -           Python 2.4.3 (#1, Sep  3 2009, 15:37:37)
[GCC 4.1.2 20080704 (Red Hat 4.1.2-46)] on linux2
Oracle Version              -           10.2.0.1

Run the following SQL statements

CREATE TABLE DEV_ROB_BUG_TEST2 (ID NUMBER);
INSERT INTO DEV_ROB_BUG_TEST2 VALUES (-1);
COMMIT;

If I run the following on the command interpreter

>>>
>>>
>>>
>>> import cx_Oracle
>>>
>>> dicOracleInfo = {}
>>> dicOracleInfo["U"]=u"XXX"
>>> dicOracleInfo["P"]=u"XXX"
>>> dicOracleInfo["IP"]=u"XXX"
>>> dicOracleInfo["Port"]=u"1521"
>>> dicOracleInfo["SID"]=u"XXX"
>>> dicOracleInfo["Trace"]=u"FALSE"
>>> dicOracleInfo["Arraysize"]=10000
>>>
>>> strTest1=u"SELECT * FROM DEV_ROB_BUG_TEST2"
>>>
>>> oraclecon  = cx_Oracle.connect(dicOracleInfo["U"],dicOracleInfo["P"],cx_Oracle.makedsn(dicOracleInfo["IP"],int(dicOracleInfo["Port"]),dicOracleInfo["SID"]))
>>> oraclecur1 = oraclecon.cursor()
>>> oraclecur2 = oraclecon.cursor()
>>>
>>> oraclecur2.arraysize = int(dicOracleInfo["Arraysize"])
>>>
>>> print strTest1
SELECT * FROM DEV_ROB_BUG_TEST2
>>> type(strTest1)
<type 'unicode'>
>>>
>>> oraclecur2.execute(strTest1)
<__builtin__.OracleCursor on <cx_Oracle.Connection to XXX@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= XXX")(PORT=1521)))(CONNECT_DATA=(SID= XXX")))>>
>>>
>>> lstRows = oraclecur2.fetchall()
>>>
>>> print lstRows
[(-1,)]

I get the correct result.  If I put the commands in a script I get the following error

-bash-3.2$ python test2.py
SELECT * FROM DEV_ROB_BUG_TEST2
Traceback (most recent call last):
  File "test2.py", line 25, in ?
    lstRows = oraclecur2.fetchall()
cx_Oracle.DatabaseError: OCI-22061: invalid format text [T
-bash-3.2$

The problem seems to be related to number types. If I use a VARHCAR2(50) there are no issues.

Thanks

Rob