Oracle 插入失败：无效月份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28211982/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle insert failure : not a valid month
提问by Traffy
I'm trying to import data from an .xlsx spreadsheet which contains date columns. In those columns, dates are displayed in the DD-MON-YY format (for example : 20-AUG-12).
我正在尝试从包含日期列的 .xlsx 电子表格导入数据。在这些列中，日期以 DD-MON-YY 格式显示（例如：20-AUG-12）。
When I'm running the import wizard, everything goes fine until I must precise the columns/fields mapping. I've got a disclaimer saying that the chosen format is not matching the table field definition (my field is a date field). An example of the insert script :
当我运行导入向导时，一切正常，直到我必须精确列/字段映射。我有一个免责声明说所选的格式与表字段定义不匹配（我的字段是日期字段）。插入脚本的一个例子：
INSERT INTO simulation
(simulation_id,
simulation_name,
sim_start_date,
sim_end_date,
status,
last_run_date,
moddat,
modusr,
notification_email)
VALUES (251.0,
'Proposal Test',
To_date('01-DEC-11', 'DD-MON-YY'),
To_date('31-DEC-11', 'DD-MON-YY'),
'C',
To_date('09-AUG-12', 'DD-MON-YY'),
To_date('09-AUG-12', 'DD-MON-YY'),
'Brent Weintz',
'[email protected]');
When I'm trying to run this query, I got the following error :
当我尝试运行此查询时，出现以下错误：
ORA-01843: not a valid month 01843. 00000 - "not a valid month"
ORA-01843: 无效月份 01843. 00000 - “无效月份”
As you can see, the formats are matching and I can't figure out where the problem is... Any idea guys?
正如你所看到的，格式是匹配的，我不知道问题出在哪里......有什么想法吗？
EDIT : My date language paramater is set to "FRENCH". Can I change it to "AMERICAN" ?
编辑：我的日期语言参数设置为“法语”。我可以把它改成“美国”吗？
采纳答案by Lalit Kumar B
Although you are using to_dateand correct format model to explicitly convert into date, still getting ORA-01843it seems like the issue is with the NLS_DATE_LANGUAGE. Read more about it here.
尽管您正在使用to_date正确的格式模型来显式转换为日期，ORA-01843但似乎问题出在NLS_DATE_LANGUAGE. 在此处阅读更多相关信息。
SQL> DROP TABLE t PURGE;
Table dropped.
SQL> CREATE TABLE t(A DATE);
Table created.
SQL> ALTER SESSION SET NLS_DATE_LANGUAGE='FRENCH';
Session altered.
SQL> SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_DATE_LANGUAGE';
PARAMETER VALUE
-------------------- ----------
NLS_DATE_LANGUAGE FRENCH
SQL> INSERT INTO t VALUES(to_date('01-jan-2012','dd-mon-yyyy'));
INSERT INTO t VALUES(to_date('01-jan-2012','dd-mon-yyyy'))
*
ERROR at line 1:
ORA-01843: not a valid month
So, I encounter the error with improper NLS_DATE_LANGUAGE. Let's set it to AMERICAN.
所以，我遇到了NLS_DATE_LANGUAGE不正确的错误。让我们将其设置为AMERICAN。
SQL> ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
Session altered.
SQL> SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_DATE_LANGUAGE';
PARAMETER VALUE
-------------------- ----------
NLS_DATE_LANGUAGE AMERICAN
SQL> INSERT INTO t VALUES(to_date('01-jan-2012','dd-mon-yyyy'));
1 row created.
SQL>
UPDATETo OP's edited question -
更新OP 编辑的问题 -
My date language paramater is set to "FRENCH". Can I change it to "AMERICAN" ?
我的日期语言参数设置为“FRENCH”。我可以把它改成“美国”吗？
From the documentation, following are the ways to set NLS parameters. Note the order of precedence.
从文档中，以下是设置 NLS 参数的方法。注意优先顺序。
回答by borjab
Try to specify the NLS_DATE_LANGUAGEas a paremeter for the TO_DATEfunction.
尝试将NLS_DATE_LANGUAGE指定为TO_DATE函数的参数。
INSERT INTO simulation
(simulation_id,
simulation_name,
sim_start_date,
sim_end_date,
status,
last_run_date,
moddat,
modusr,
notification_email)
VALUES (251.0,
'Proposal Test',
To_date('01-DEC-11', 'DD-MON-YY', 'NLS_DATE_LANGUAGE = AMERICAN'),
To_date('31-DEC-11', 'DD-MON-YY', 'NLS_DATE_LANGUAGE = AMERICAN'),
'C',
To_date('09-AUG-12', 'DD-MON-YY', 'NLS_DATE_LANGUAGE = AMERICAN'),
To_date('09-AUG-12', 'DD-MON-YY', 'NLS_DATE_LANGUAGE = AMERICAN'),
'Brent Weintz',
'[email protected]');
Of course, you can change it with the alter session if you are going to execute a few queries.
当然，如果您要执行一些查询，您可以使用 alter session 更改它。
