Oracle插入失败:不是有效的一个月 [英] Oracle insert failure : not a valid month
问题描述
我正在尝试从包含日期列的.xlsx电子表格导入数据。在这些列中,日期以DD-MON-YY格式显示(例如:20-AUG-12)。
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).
当我运行导入向导时,一切顺利,直到我必须精确列/字段映射。我有一个免责声明说,所选格式与表字段定义不匹配(我的字段是日期字段)。插入脚本的示例:
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',
'bweintz@carlsonwagonlit.com');
当我尝试运行此查询时,我收到以下错误:
When I'm trying to run this query, I got the following error :
ORA-01843:不是有效月份
01843. 00000 - 不是有效月份
ORA-01843: not a valid month 01843. 00000 - "not a valid month"
正如你所看到的,格式是匹配的,我无法弄清楚问题在哪里...任何想法家伙?
As you can see, the formats are matching and I can't figure out where the problem is... Any idea guys?
编辑:我的日期语言参数设置为FRENCH。可以将其更改为AMERICAN吗?
EDIT : My date language paramater is set to "FRENCH". Can I change it to "AMERICAN" ?
推荐答案
尽管您使用 to_date
和正确的格式模型显式转换为日期,仍然得到 ORA-01843
似乎问题是与 NLS_DATE_LANGUAGE
。详细了解 here 。
Although you are using to_date
and correct format model to explicitly convert into date, still getting ORA-01843
it seems like the issue is with the NLS_DATE_LANGUAGE
. Read more about it here.
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
所以,我遇到错误,不正确的 NLS_DATE_LANGUAGE 。让我们将其设置为美国。
So, I encounter the error with improper NLS_DATE_LANGUAGE. Let's set it to 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>
更新对OP编辑的问题 -
UPDATE To OP's edited question -
我的日期语言参数设置为FRENCH。可以将其更改为AMERICAN吗?
My date language paramater is set to "FRENCH". Can I change it to "AMERICAN" ?
从文档,以下是设置NLS参数的方法。注意优先顺序。
>
这篇关于Oracle插入失败:不是有效的一个月的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!