Liquibase脚本返回ORA-01843:无效月份 [英] Liquibase script returns ORA-01843: not a valid month

问题描述

我在使用以下liquibase脚本时遇到麻烦:

I have trouble with the following liquibase script:

<sql>
        MERGE INTO A config
        USING (SELECT 100 as id, '02.01.15 12:00:00' as CHANGED, 0 as DELETED, 1 as B FROM DUAL) src ON (src.id = config.id)
        WHEN NOT MATCHED THEN 
            INSERT(id,CHANGED, DELETED, B) VALUES(src.id, src.CHANGED, src.DELETED, src.B)
        WHEN MATCHED THEN
            UPDATE SET config.B = src.B;
 </sql>

当我在sql标记之间插入原始代码并在数据库下运行(在SQL Developer中)时，结果是:合并了1行.

When I insert raw code between sql tags and run under the database (in SQL Developer), the result is: 1 row merged.

通过liquibase运行此程序时，出现错误 ORA-01843:无效月份.

When I run this via liquibase I get error ORA-01843: not a valid month.

怎么可能?

推荐答案

'02.01.15 12:00:00'不是日期，而是字符串.如果您尝试将其插入到DATE数据类型列中，则Oracle将尝试使用以下等价方式将其转换为日期:

'02.01.15 12:00:00' is not a date it is a string; if you are trying to insert it into a DATE data type column then Oracle will try to cast it to a date using the equivalent of:

SELECT TO_DATE(
         '02.01.15 12:00:00',
         ( SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT' )
       ) as CHANGED
FROM   DUAL

因此，如果您的NLS_DATE_FORMAT会话参数与字符串'02.01.15 12:00:00'的格式不匹配，则它将引发异常-这就是自获取ORA-01843: not a valid month以来似乎正在发生的事情.

So if your NLS_DATE_FORMAT session parameter does not match the format of your string '02.01.15 12:00:00' then it will raise an exception - and this is what is appearing to happen since you are getting ORA-01843: not a valid month.

最好的解决方案是修改脚本以将字符串显式转换为日期:

The best solution is to modify your script to explicitly cast the string to a date:

MERGE INTO A config
USING (
  SELECT 100 as id,
         TO_DATE( '02.01.15 12:00:00', 'DD.MM.YY HH24:MI:SS' ) as CHANGED,
         0 as DELETED,
         1 as B
  FROM   DUAL
) src ON (src.id = config.id)
WHEN NOT MATCHED THEN 
  INSERT(id,CHANGED, DELETED, B) VALUES(src.id, src.CHANGED, src.DELETED, src.B)
WHEN MATCHED THEN
  UPDATE SET config.B = src.B;

或使用时间戳文字:TIMESTAMP '2015-01-02 12:00:00'

但是您也可以创建一个登录触发器来更改NLS_DATE_FORMAT会话参数.将触发器包装在以下代码周围:

But you could also create a logon trigger to change the NLS_DATE_FORMAT session parameter. Wrap the trigger around this code:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YY HH24:MI:SS';

但是，这会将所有隐式转换中使用的日期格式从字符串更改为日期(反之亦然)，因此可能会破坏其他也依赖于此类隐式转换的查询.另外，每个用户都可以随时更改其会话参数，因此在登录时设置此默认值取决于他们在会话期间从不更改它.

However, this will change the date format used in all implicit conversions from string to date (and vice versa) so may break other queries that also rely on such implicit conversions. Also, each user can change their session parameters at any time so setting this default at log on relies on them never changing it during their session.

[TL; DR] 修复脚本，不要在数据类型之间使用隐式转换，而不是修改用于隐式转换的格式模型.

[TL;DR] Fix your script to not use implicit conversions between data types rather than modifying the format model used for implicit conversions.

这篇关于Liquibase脚本返回ORA-01843:无效月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！