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>

当我通过liquibase运行它时，我得到错误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:不是一个有效的月份而出现的情况。

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

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;

但是也可以创建登录触发器来更改NLS_DATE_FORMAT会话参数。将触发器环绕在以下代码中：

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

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

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