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.
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();