Flyway:将视图创建为WITH(公共表表达式CTE) [英] Flyway: create view as WITH (common table expression CTE)

问题描述

如何克服该错误?

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]

当我将WITH子句作为子查询移到FROM子句时，脚本成功.但是以这种方式重构可能会导致其他视图效率低下.

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.

推荐答案

根本原因在于Flyway的Oracle解析器:

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

这篇关于Flyway:将视图创建为WITH(公共表表达式CTE)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！