Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Oracle: Parser fails on VIEW ... WITH not followed by PL/SQL #2252

Closed
apexbine opened this issue Jan 4, 2019 · 2 comments
Closed

Oracle: Parser fails on VIEW ... WITH not followed by PL/SQL #2252

apexbine opened this issue Jan 4, 2019 · 2 comments

Comments

@apexbine
Copy link

@apexbine apexbine commented Jan 4, 2019

Which version and edition of Flyway are you using?

Flyway Community Edition 5.2.4

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

Command Line

Which database are you using (type & version)?

Oracle 12.2

Which operating system are you using?

Windows 10

What did you do?

(Please include the content causing the issue, any relevant configuration settings, the SQL statement that failed (if relevant) and the command you ran.)

Ran this statement, which worked perfectly in SQL Developer:

create or replace view test_view
as
  with b_soll
       as ( 
           select 'c' eins
                , 'd' zwei
                , 'e' drei               
             from dual )
     , b_ist
       as ( 
           select 'c' vier
                , '1'  fuenf
                , 'd' sechs
             from dual )
  ( select 'SOLL' soll
         , eins
         , zwei
         , drei
      from b_soll
   minus
   select 'SOLL' soll
        , vier
        , fuenf
        , sechs
     from b_ist )
  union all
  ( select 'IST' ist
         , vier
         , fuenf
         , sechs
      from b_ist
   minus
   select 'IST' ist
        , eins
        , zwei
        , drei
     from b_soll );
What did you expect to see?

Success message

What did you see instead?

SQL State : 42000
Error Code : 933
Message : ORA-00933: SQL command not properly ended

@apexbine
Copy link
Author

@apexbine apexbine commented Feb 4, 2019

Hi Axel,

here's another test case.

Failed (ORA-00933: SQL command not properly ended):
create or replace view flyway_test as with eins as (select dummy from dual) select * from eins order by 1 ;

Worked (just removed the semicolon at the end):
create or replace view flyway_test as with eins as (select dummy from dual) select * from eins order by 1

Worked (no with but semicolon);
create or replace view flyway_test as select dummy from dual order by 1;

@axelfontaine axelfontaine changed the title ORA-00933: SQL command not properly ended (View with WITH and MINUS) Oracle: Parser fails on VIEW ... WITH not followed by PL/SQL Feb 7, 2019
axelfontaine pushed a commit to flyway/flywaydb.org that referenced this issue Feb 7, 2019
@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Feb 7, 2019

Fixed. It turned out to be a delimiter issue. We wrongly assumed that it was always / after CREATE VIEW ... AS WITH, but as this case demonstrated, this assumption was too imprecise.

dohrayme pushed a commit to dohrayme/flyway that referenced this issue Feb 3, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants