cx_Oracle'ORA-01843:无效月份'，带有unicode参数 [英] cx_Oracle 'ORA-01843: not a valid month' with unicode parameter

问题描述

我有以下内容:(使用ipython)

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))]

由于某种原因，我不能将unicode字符串用作时间戳参数(IN [37])，更奇怪的是，在执行此操作后，我需要重置会话NLS格式，然后才能再次使用普通字符串.

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.

我正在使用: Cx_Oracle 5.1.2 python 2.7.3 Oracle 10.2.0.1.0

I'm using: Cx_Oracle 5.1.2 python 2.7.3 Oracle 10.2.0.1.0

有什么想法吗?

感谢您的阅读时间.

推荐答案

实际上是Oracle 10.5.0.2和11.2.0.1.中的错误.

It's actually a bug in the Oracle 10.5.0.2 and 11.2.0.1.

Bug可以复制如下:

Bug can be reproduced as following:

在会话中设置NLS_TIMESTAMP_FORMAT.

set NLS_TIMESTAMP_FORMAT in session.

使用Unicode数据运行任何隐式或显式的TO_DATE转换.

Run any implicit or explicit TO_DATE conversion with unicode data.

下一个带有Unicode数据的隐式或显式TO_TIMESTAMP将触发时间戳格式的内部重置.

Next implicit or explicit TO_TIMESTAMP with unicode data will trigger internal reset of timestamp format.

所有连续的TO_TIMESTAMP将失败，时间戳的TO_CHAR将产生无效的输出.

All consecutive TO_TIMESTAMP will fail and TO_CHAR of timestamp will produce invalid output.

这是测试行为的代码:

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;

这篇关于cx_Oracle'ORA-01843:无效月份'，带有unicode参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！