Liquibase 3.3 : insert SYSDATE into DATE field fails with 'ora-01858'

General Discussion
1

Are you using an tag? Or is it a raw block?

Nathan

1 Like
2

Hi Nathan,

It’s using liquibase tags:

    <insert tableName="DWP_VERSION"></div>

        <column name="VER_ID_VERSION_MAJOR" value="3"/></div>

        <column name="VER_ID_VERSION_MINOR" value="0"/></div>

        <column name="VER_LIBELLE_VERSION" value="INFOCENTRE V3.00"/></div>

        <column name="VER_DATE" value="SYSDATE"/></div>

    </insert></div></div>

With VER_DATE being a DATE column.

        <column name="VER_DATE" value="SYSTIMESTAMP"/></div></div>

works fine.

Liquibase is on a french windows , database on english linux, and oracle sessions set in french with :

view NLS_SESSION_PARAMETERS says

NLS_DATE_FORMAT : DD/MM/RR

NLS_TIMESTAMP_FORMAT : DD/MM/RR HH24:MI:SSXFF

but view NLS_DATABASE_PARAMETERS says

NLS_DATE_FORMAT : DD/MON/RR

NLS_TIMESTAMP_FORMAT : DD-MON-RR HH.MI.SSXFF AM

Regards

1 Like
3

Hello

I’ve just upgraded to liquibase 3.3 (was in 1.9.5) because logging in debug mode didn’t work.

Now logging works but I spent a lot of time to find why inserting the current date in a date field didn’t work :

Oracle 11g2 : “VER_DATE DATE”

INSERT INTO DWH_GTO.DWP_VERSION (VER_ID_VERSION_MAJOR, VER_ID_VERSION_MINOR, VER_LIBELLE_VERSION, VER_DATE) VALUES (‘3’, ‘0’, ‘INFOCENTRE V3.00’, ‘SYSDATE’)

Liquibase.exception.DatabaseException: java.sql.SQLDataException: ORA-01858: a non-numeric character found where a digit was expected

This was working before.

And this unexpectedly works :

INSERT INTO DWH_GTO.DWP_VERSION (VER_ID_VERSION_MAJOR, VER_ID_VERSION_MINOR, VER_LIBELLE_VERSION, VER_DATE) VALUES (‘3’, ‘0’, ‘INFOCENTRE V3.00’, ‘SYSTIMESTAMP’)

I’ve not found anything about that in the liquibase documentation. I believe it’s a Locale date problem, then how to format the date as expected?

Regards

Frederic

4

It should work if you use valueDate=“SYSDATE” instead of value=“SYSDATE”.

Liquibase doesn’t inspect the table to determine the type, so value="" normally assumes it’s a string and needs to be quoted but there is a special case for the “current time” function which is set to SYSTIMESTAMP for oracle.

Using valueDate instead tells liquibase the value is a date and if it’s a known date function to not quote it which should resolve your issue.

Nathan

5

Hi

sorry I forgot to give a feedback.

That worked fine!

Thanks.

6

Good, thanks for the update.

Nathan

7

This solution using SYSTIMESTAMP instead of SYSDATE worked for me. Thank you.