dateLiteral does not work on OracleDB 12c or 19c

[dakriy]

The dateLiteral function does not work on OracleDB (and I suspect the other date/datetime literal functions also do not work for the same reason). The following error is generated:

org.jetbrains.exposed.exceptions.ExposedSQLException: java.sql.SQLDataException: ORA-01861: literal does not match format string

here is the relevant subset of the query that was executed

TABLE.DATE_COL = (SELECT MAX (TABLE.DATE_COL)
    FROM TABLE TABLE_ALIAS
    WHERE (TABLE_ALIAS.DATE_COL <= '2021-09-08')
    AND (TABLE_ALIAS.PK = TABLE.PK))

The generated sql for oracle should be something along the lines of:

TABLE.DATE_COL = (SELECT MAX (TABLE.DATE_COL)
    FROM TABLE TABLE_ALIAS
    WHERE (TABLE_ALIAS.DATE_COL <= TO_DATE('2021-09-08', 'YYYY-MM-DD'))
    AND (TABLE_ALIAS.PK = TABLE.PK))

Current workarounds are a custom dateliteral function like this:

fun getSQLDateExpression(date: LocalDate): Expression<*> {
    return if (currentDialect is OracleDialect) {
        CustomDateFunction("TO_DATE", dateLiteral(date), stringLiteral("YYYY-MM-DD"))
    } else {
        dateLiteral(date)
    }
}

OR not using a date literal and using a bind variable.

Tested on oracle 12c and 19c.