Oracle statement fails when it contains 'string'as and no space in between

[svitalsky]

What version of Flyway are you using?

4.0.3
4.1.2

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, version is irrelevant here (and I don't know right away)

What operating system are you using?

Windows 7 (ashamed of it, but it's a corporate env.)

What did you do?

I've run flyway:migrate command with a script containing "create or replace procedure" command. First within a script with a lot of other DDL commands, but for this one the slash at the end of the procedure definition was not removed but rather incorporated into the proc. body with all that followed.

Then I've tried to put that procedure into separate script: without slash it worked OK (so the SQL itself is correct), with the slash at the end it did not work (slash was added to the procedure body). Eventually, after removing parts of the procedure and trying again and again I've found out that the offending code was this:

select ... , '1'as "QUANTITY", ...

Notice the missing space between the second apostrophe and the "as" keyword (I didn't write the SQL, however it is perfectly legal to write it this way, and after all without slash it worked)! When I added a space there the script worked just fine. I suppose Flyway does some parsing and this missing space causes it to fail.

What did you expect to see?

Successful migration.

What did you see instead?

Unsuccessful migration with one stored proc not compiled and some other stuff missing (swallowed by that stored procedure not knowing where its end was).

Thank you.

[axelfontaine]

Thanks for analysing. The as keyword with no space preceding it was indeed the issue.

[Nthalk]

@aaltergot There is a missing keyword in this list: DATE, the oracle date cast keyword.

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/22/2017'; -- << With a space between DATE and ', it works fine. Otherwise no.
  END F_MAGIC_DATE;

END DEMO_P_FLYWAYBUG;
/