Flyway: create view as WITH (common table expressi

Question

How to overcome that error?

Java version: 1.8.0_131, vendor: Oracle Corporation, runtime: C:\Program Files\Java\jdk1.8.0_131\jre    
[DEBUG]    com.oracle:ojdbc8:jar:12.2.0.1.0:provided    
[INFO] Flyway Community Edition 5.2.4 by Boxfuse
[INFO] Database: jdbc:oracle:thin:@bdlg3400.na.pg.com:1525:ioptd101 (Oracle 12.2)
[DEBUG] Driver  : Oracle JDBC driver 12.2.0.1.0


[ERROR] Migration R__SOME_VIEW_VW.sql failed
[ERROR] --------------------------------------
[ERROR] SQL State  : 42000
[ERROR] Error Code : 933
[ERROR] Message    : ORA-00933: (non-english description)
[ERROR] Location   : sql\Views\R__SOME_VIEW_VW.sql (...\sql\Views\R__SOME_VIEW_VW.sql)
[ERROR] Line       : 7
[ERROR] Statement  : CREATE OR REPLACE VIEW some_view_vw as
[ERROR] WITH
[ERROR] abc AS
[ERROR] (
[ERROR]     SELECT
[ERROR]         iglp.p_skid,
[ERROR]         LISTAGG(g.g_code, ',') WITHIN GROUP (ORDER BY g.g_code) AS lokd_gate_lst
[ERROR]     FROM ig_l_prod iglp
[ERROR]     JOIN ig_prc ig ON ig.ig_skid = iglp.ig_skid
[ERROR]     JOIN g g ON g.g_skid = ig.g_skid
[ERROR]     WHERE iglp.lock_ind = 'Y'
[ERROR]     GROUP BY
[ERROR]         iglp.p_skid
[ERROR] )
[ERROR] SELECT
[ERROR]     pr.p_skid AS scr_prod_skid,
[ERROR]     lg.lokd_gate_lst,
[ERROR]     pr.*
[ERROR] FROM p pr
[ERROR] LEFT JOIN lokd_gate lg ON lg.p_skid = pr.p_skid
[ERROR] where exists(select 1 from PP_PRC pipo WHERE pipo.PI_P_SKID = pr.P_SKID);
[ERROR]
[ERROR] -> [Help 1]

When I move the WITH clause as a sub-query to FROM clause, script is successful. But refactoring this way may cause other views inefficient.

Answer 1

The root cause lies in the Flyway's Oracle parser:

OracleParser.java

private static final Pattern PLSQL_VIEW_REGEX = Pattern.compile(
        "^CREATE(\\sOR\\sREPLACE)?(\\s(NON)?EDITIONABLE)?\\sVIEW\\s.*\\sAS\\sWITH\\s(PROCEDURE|FUNCTION)");
private static final StatementType PLSQL_VIEW_STATEMENT = new StatementType();

Answer 2

The error message is ORA-00933 sql command not properly ended, and this may mean that you have some prohibited or conflicting clauses in your query, but in my experience this typically means a comma is missing somewhere or there is a typo.

First some dummy tables:

create table ig_l_prod(
  p_skid number,
  ig_skid number,
  lock_ind varchar2(1)
);
create table ig_prc(
  ig_skid number,
  g_skid number
);
create table g(
  g_skid number,
  g_code varchar2(1)
);
create table p(
  p_skid number,
  name varchar2(10)
);
create table PP_PRC(
PI_P_SKID number
);

This is the query from your log, with one modification:

CREATE OR REPLACE VIEW some_view_vw as
WITH
lokd_gate AS -- *** Replaced "abc" with "lokd_gate" ***
(
    SELECT
        iglp.p_skid,
        LISTAGG(g.g_code, ',') WITHIN GROUP (ORDER BY g.g_code) AS lokd_gate_lst
    FROM ig_l_prod iglp
    JOIN ig_prc ig ON ig.ig_skid = iglp.ig_skid
    JOIN g g ON g.g_skid = ig.g_skid
    WHERE iglp.lock_ind = 'Y'
    GROUP BY
        iglp.p_skid
)
SELECT
    pr.p_skid AS scr_prod_skid,
    lg.lokd_gate_lst,
    pr.*
FROM p pr
LEFT JOIN lokd_gate lg ON lg.p_skid = pr.p_skid
where exists(select 1 from PP_PRC pipo WHERE pipo.PI_P_SKID = pr.P_SKID);

The CTE was aliased as abc, while the join is to lokd_gate--this might just be an artifact of sanitizing the view to post here, but if it's in the actual SQL, then the query has a problem.

With the dummied up tables, this SQL executed without error in Oracle 12c.

Try executing the SQL as shown above from SQL*Plus and see if it works. If so, then it's the fault of the framework not parsing CTEs well (not unheard of).