Liquibase script returns ORA-01843: not a valid mo

Question

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>

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

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

How is it possible?

Answer 1

'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

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;

or to use a timestamp literal: TIMESTAMP '2015-01-02 12:00:00'

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] Fix your script to not use implicit conversions between data types rather than modifying the format model used for implicit conversions.

Answer 2

Use this script:

<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>