Liquibase脚本返回ORA-01843:无效月份

Question

我对下面的清算脚本有困难：

<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标记之间插入原始代码并在数据库下运行时(在中)，结果是:1行合并。

当我通过清算库运行这个程序时，我得到了错误ORA-01843:不是一个有效的月份。

这怎么可能呢？

Answer 1

'02.01.15 12:00:00'不是日期，而是字符串；如果您试图将它插入到DATE数据类型列中，那么Oracle将尝试使用以下方法将其转换为一个日期：

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以来所发生的事情。

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

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会话参数。将触发器封装在以下代码周围：

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

但是，这将改变从字符串到日期的所有隐式转换中使用的日期格式(反之亦然)，因此可能会中断也依赖这种隐式转换的其他查询。而且，每个用户都可以在任何时候更改他们的会话参数，因此在登录时设置这个默认参数依赖于他们在会话期间从不更改它。

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

Answer 2

使用以下脚本：

<sql>
    MERGE INTO A config
    USING (SELECT 100 as id, TO_DATE('2015/02/01 12:00:00','YYYY/MM/DD 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;
</sql>