cx_Oracle 'ORA-01843: not a valid month' with unicode parameter
cx-oracle, oracle, python, unicode
Solution
It's actually a bug in the Oracle 10.5.0.2 and 11.2.0.1.
Bug can be reproduced as following:
set NLS_TIMESTAMP_FORMAT in session.
Run any implicit or explicit TO_DATE conversion with unicode data.
Next implicit or explicit TO_TIMESTAMP with unicode data will trigger internal reset of timestamp format.
All consecutive TO_TIMESTAMP will fail and TO_CHAR of timestamp will produce invalid output.
Here is the code to test behaviour:
ALTER SESSION SET NLS_TERRITORY = 'AMERICA';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
REM --- WORKS:
SELECT TO_TIMESTAMP('2013-06-24 18:15:10.312') FROM DUAL;
REM --- WORKS:
SELECT TO_DATE('2013-06-24 18:15:10') FROM DUAL;
REM --- WORKS:
SELECT TO_TIMESTAMP('2013-06-24 18:15:10.312') FROM DUAL;
REM --- WORKS:
SELECT TO_TIMESTAMP(x) FROM (SELECT CAST('2013-06-24 18:15:10.312' AS NVARCHAR2(30)) AS X FROM DUAL);
REM --- WORKS:
SELECT TO_DATE(x) FROM (SELECT CAST('2013-06-24 18:15:10' AS NVARCHAR2(30)) AS X FROM DUAL);
REM --- WORKS:
SELECT TO_TIMESTAMP('2013-06-24 18:15:10.312') FROM DUAL;
REM !!! FAILS!
SELECT TO_TIMESTAMP(x) FROM (SELECT CAST('2013-06-24 18:15:10.312' AS NVARCHAR2(30)) AS X FROM DUAL);
REM !!! FAILS!
SELECT TO_TIMESTAMP('2013-06-24 18:15:10.312') FROM DUAL;
REM --- WORKS:
SELECT TO_DATE('2013-06-24 18:15:10') FROM DUAL;
Problem
I have the following: (using ipython) ``` In [30]: con = cx_Oracle.connect('refill_test02/******@MYDB') In [31]: cur = con.cursor() In [32]: cur.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'") In [33]: cur.execute("select to_date(:0), to_timestamp(:1) from dual", ['2013-03-12', '2013-03-12 08:22:31.332144']) Out[33]: <__builtin__.OracleCursor on <cx_Oracle.Connection to refill_test02@MYDB>> In [34]: cur.fetchall() Out[34]: [(datetime.datetime(2013, 3, 12, 0, 0), datetime.datetime(2013, 3, 12, 8, 22, 31, 332144))] In [35]: cur.execute("select to_date(:0), to_timestamp(:1) from dual", [u'2013-03-12', '2013-03-12 08:22:31.332144']) Out[35]: <__builtin__.OracleCursor on <cx_Oracle.Connection to refill_test02@MYDB>> In [36]: cur.fetchall() Out[36]: [(datetime.datetime(2013, 3, 12, 0, 0), datetime.datetime(2013, 3, 12, 8, 22, 31, 332144))] In [37]: cur.execute("select to_date(:0), to_timestamp(:1) from dual", [u'2013-03-12', u'2013-03-12 08:22:31.332144']) --------------------------------------------------------------------------- DatabaseError Traceback (most recent call last) /home/xxxxx/<ipython-input-37-8af80e5fc40c> in <module>() ----> 1 cur.execute("select to_date(:0), to_timestamp(:1) from dual", [u'2013-03-12', u'2013-03-12 08:22:31.332144']) DatabaseError: ORA-01843: not a valid month In [38]: cur.execute("select to_date(:0), to_timestamp(:1) from dual", [u'2013-03-12', '2013-03-12 08:22:31.332144']) --------------------------------------------------------------------------- DatabaseError Traceback (most recent call last) /home/xxxx/<ipython-input-38-bc628f006aa3> in <module>() ----> 1 cur.execute("select to_date(:0), to_timestamp(:1) from dual", [u'2013-03-12', '2013-03-12 08:22:31.332144']) DatabaseError: ORA-01843: not a valid month In [39]: cur.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'") In [40]: cur.execute("select to_date(:0), to_timestamp(:1) from dual", [u'2013-03-12', '2013-03-12 08:22:31.332144']) Out[40]: <__builtin__.OracleCursor on <cx_Oracle.Connection to refill_test02@MYDB>> In [41]: cur.fetchall() Out[41]: [(datetime.datetime(2013, 3, 12, 0, 0), datetime.datetime(2013, 3, 12, 8, 22, 31, 332144))] ``` For some reason, I can't use an unicode string for a timestamp parameter (IN[37]), what's even more strange is that after I do it I need to reset the session NLS formats before it works again with a normal string. I'm using: Cx_Oracle 5.1.2 python 2.7.3 Oracle 10.2.0.1.0 Any ideas? Thank you for your time on reading this.