Oracle插入时间戳 - 需要AM / A.M.或PM / P.M.

Question

在SQL Developer中，我创建了一个导出文件，.sql文件（非常大）。它包含数百个像这样的插入语句：

"Insert into SCHEMA.TABLE (
 ... ,
 CREATEDATE,
 MODIFIEDDATE,
 ....
) 
values (
 ... , 
 to_timestamp('20-AUG-12 01.09.53.271000000 AM','DD-MON-RR HH.MI.SS.FF AM'),
 to_timestamp('20-AUG-12 01.09.53.271000000 PM','DD-MON-RR HH.MI.SS.FF PM'),
 ...
);"

当我在SQL Developer中运行它时，很好用。但我无法使用SQL Developer导入一些.sql文件（或者我不知道如何操作）。

当我使用SQL * Plus运行它时，会出现以下错误：

ORA-01855：需要AM / A.M.或PM / P.M。

如果我从插入语句中删除AM / PM关键字，则SQL * Plus可以插入行，否则就没有成功。AM / PM有什么问题吗？

以下是确切的插入命令：

Insert into SCHEMA.TABLE(

ENTRYID,GROUPID,COMPANYID,USERID,USERNAME,CREATEDATE,MODIFIEDDATE,
CLASSNAMEID,CLASSPK,CLASSUUID,VISIBLE,STARTDATE,ENDDATE,PUBLISHDATE,
EXPIRATIONDATE,MIMETYPE,TITLE,DESCRIPTION,SUMMARY,URL,HEIGHT,WIDTH,
PRIORITY,VIEWCOUNT

) values (

11902,11005,10136,10178,'Test Test',
to_timestamp('20-AUG-12 01.09.53.271000000 AM','DD-MON-RR HH.MI.SS.FF AM'),
to_timestamp('20-AUG-12 01.09.53.271000000 PM','DD-MON-RR HH.MI.SS.FF PM'),
10076,11900,'AAA',1,null,null,null,null,null,
'ABC','XyZ',null,null,0,0,0,2563

);

- Mato.Duris

---

AM/PM 不应该有任何问题；在 SQL*Plus 中将 to_timestamp() 调用插入到具有 timestamp 列的表中可以正常工作。据我所知，AM/PM 文字值不受 NLS 设置的影响，因此我认为这可能不是问题所在。我必须假设您已经发布了 确切 的运行内容；完整的命令（未更改名称）会给人更多信心，以及已经起作用的修改版本。SQL Developer 的问题是什么；您可以打开脚本文件，然后“运行脚本”吗？ - Alex Poole

你能发布一份SQL文件中的数据样本吗？时间格式是否总是使用 'DD-MON-RR HH.MI.SS.FF AM'，还是总是/有时使用 'DD-MON-RR HH24.MI.SS.FF'？ - MT0

哦，原来子午线指示器是NLS敏感的。在SQL*Plus中执行select value from nls_session_parameters where parameter = 'NLS_DATE_LANGUAGE'会得到什么结果？希望不是英语；斯洛伐克语将是一个好答案... - Alex Poole

Answer 1

我能立即想到的唯一复现此问题的方法是更改 NLS 设置；但不要更改 NLS_LANGUAGE，因为那样也会影响错误消息。使用英语可以，就像你预期的那样：

SQL> alter session set NLS_DATE_LANGUAGE='ENGLISH';

Session altered.

SQL> select value from nls_session_parameters
  2  where parameter = 'NLS_DATE_LANGUAGE';

VALUE
--------------------------------------------------------------------------------
ENGLISH

SQL> insert into t42 (createddate)
  2  values (to_timestamp('20-08-12 01.09.53.271000000 AM','DD-MM-RR HH.MI.SS.FF PM'));

1 row created.

仅更改NLS_DATE_LANGUAGE就会复制您的错误：

alter session set NLS_DATE_LANGUAGE='SLOVAK';

Session altered.

SQL> insert into t42 (createddate)
  2  values (to_timestamp('20-AUG-12 01.09.53.271000000 AM','DD-MON-RR HH.MI.SS.FF PM'));
values (to_timestamp('20-AUG-12 01.09.53.271000000 AM','DD-MON-RR HH.MI.SS.FF PM'))
                     *
ERROR at line 2:
ORA-01855: AM/A.M. or PM/P.M. required

直接更改NLS_LANGUAGE或通过影响它的某些内容会使错误消息显示方式发生变化：

SQL> alter session set NLS_LANGUAGE = 'SLOVAK';

Relácia zmenená.

SQL> insert into t42 (createddate)
  2  values (to_timestamp('20-AUG-12 01.09.53.271000000 AM','DD-MON-RR HH.MI.SS.FF PM'));
values (to_timestamp('20-AUG-12 01.09.53.271000000 AM','DD-MON-RR HH.MI.SS.FF PM'))
                     *
ERROR v riadku 2:
ORA-01855: vy¿aduje sa AM/A.M. alebo PM/P.M.

您需要一种仍将AUG识别为有效月份但不使用AM/PM的语言; 斯洛伐克语似乎符合要求，所以我猜您可能来自那里。其他语言也可能会这样做。当NLS_LANGUAGE仍然设置时出现错误消息显示AM / PM，但to_char（sysdate，'HH AM')指示该消息是错误的。只设置NLS_DATE_LANGUAGE的情况下，消息无论如何都是英文（例如尝试将其与土耳其语一起使用，但您必须更改月份名称或切换使用月份数字），并且始终显示AM / PM。

如果是这种情况，可以在运行此脚本之前将NLS_DATE_LANGUAGE更改为英语；或在执行导出之前将SQL Developer设置更改为使用斯洛伐克语（或实际使用的任何语言）-这将希望导出使用DOPOLUDNIE/POPOLUDNIE，尽管如果没有也不会完全让我感到惊讶；或将SQL Developer NLS_TIMESTAMP_FORMAT更改为使用24小时制时间（以及4位数年份和月份数字而不是名称）进行导出，这将完全避免该问题。或者当然可以从SQL Developer运行脚本。

- Alex Poole

---

谢谢大家！！！添加这个“alter session set NLS_DATE_LANGUAGE='ENGLISH';”帮了我...非常感谢。 - Mato.Duris

Answer 2

Alex的回答非常好，但是如果您能稍微修改生成的插入语句，就有一个更简单的解决方案。TO_TIMESTAMP()有第三个参数，可以在插入本身中指定NLS_DATE_LANGUAGE。

create table a ( tstamp timestamp );

Table created.

insert into a
values (to_timestamp( '20-08-12 01.09.53.271000000 AM'
                    , 'DD-MM-RR HH.MI.SS.FF PM'
                    , 'nls_date_language = ENGLISH'));

1 row created.

select *
  from a;

TSTAMP
------------------------------------------------------
20-AUG-12 01.09.53.271000

您不需要为会话更改日期语言，也不需要事后将其更改回来。

- Ben

---

如果它们来自 SQL 开发人员的“导出”，那么逐个修改它们会有点麻烦，但是这确实是一个不错的替代方案。 - Alex Poole

我不知道 @Alex... 我希望 Oracle 的工具可以让你使用 Oracle，但是在过去我对此的判断是错误的 :-). - Ben

Answer 3

没有数据的话我不能确定，但听起来你的“大SQL文件”并不总是（或有时）按照你期望的格式使用时间戳，并且没有使用AM或PM。

尝试使用24小时制而不是AM或PM，看看是否与“大SQL文件”中的数据匹配：

 ...
 to_timestamp('20-AUG-12 01.09.53.271000000','DD-MON-RR HH24.MI.SS.FF'),
 to_timestamp('20-AUG-12 13.09.53.271000000','DD-MON-RR HH24.MI.SS.FF'),
 ...

- MT0