Oracle statement fails when it contains DATE'11/29/2017' and no space in between

[Nthalk]

What version of Flyway are you using?

4.2.0

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin, SBT plugin, ANT tasks)

Maven plugin

What database are you using (type & version)?

Oracle 11G

What operating system are you using?

Linux

What did you do?

(Please include the content causing the issue, any relevant configuration settings, and the command you ran)

CREATE OR REPLACE PACKAGE DEMO_P_FLYWAYBUG
IS
  FUNCTION F_MAGIC_DATE
    RETURN DATE;
END DEMO_P_FLYWAYBUG;
/

CREATE OR REPLACE PACKAGE BODY DEMO_P_FLYWAYBUG
IS
  FUNCTION F_MAGIC_DATE
    RETURN DATE
  IS BEGIN
    RETURN DATE'11/29/2017';
  END F_MAGIC_DATE;

END DEMO_P_FLYWAYBUG;
/

What did you expect to see?

That the USER_SOURCE for the package body object ends with 'END DEMO_P_FLYWAYBUG'
and that the package compiles.

What did you see instead?

That the USER_SOURCE for the package body object ends with '/'
and that the package does not compile.

I traced this bug to

flyway/flyway-core/src/main/java/org/flywaydb/core/internal/database/SqlStatementBuilder.java

Line 193 in df9e756

if (endWithOpenMultilineStringLiteral() || insideMultiLineComment || isSingleLineComment(lineSimplified)) {

where it thinks it is in a multiline string.

This is because the DATE cast keyword is allowed before the strings. This is fixed with a space between DATE and the subsequent quote.

This can easily be fixed by adding DATE to the following list

flyway/flyway-core/src/main/java/org/flywaydb/core/internal/database/oracle/OracleSqlStatementBuilder.java

Line 37 in df9e756

private static final Pattern KEYWORDS_BEFORE_STRING_LITERAL_REGEX = Pattern.compile("^(N|IF|ELSIF|SELECT|IMMEDIATE|RETURN|IS)('.*)");

[axelfontaine]

Good catch and excellent analysis! That was indeed the issue. Thank you very much. Fixed!