飞速:与(共同表达式CTE)创建视图

Question

如何克服该错误？

Java version: 1.8.0_131, vendor: Oracle Corporation, runtime: C:Program FilesJavajdk1.8.0_131jre    
[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   : sqlViewsR__SOME_VIEW_VW.sql (...sqlViewsR__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]

当我将条款作为子问题从子句中移动时，脚本就成功了。但是以这种方式进行重构可能会导致其他视图效率低下。

Answer 1

根本原因位于飞速的甲骨文解析器中：

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

错误消息是 ORA-00933 sql command not properly ended，这可能意味着您在查询中有一些禁止或相互冲突的子句，但是根据我的经验，这通常意味着逗号在某个地方缺失或有错别字。

首先一些虚拟表：

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

这是您日志的查询，并进行了一个修改：

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

CTE被称为abc，而JOIN是与lokd_gate混合在一起 - 这可能只是对在此处发布的视图进行消毒的伪像，但是如果它在实际的SQL中，则查询存在问题。

使用虚拟表格，该SQL在Oracle 12c中执行而没有错误。

尝试执行SQL，如上所述，从SQL*Plus中显示，看看它是否有效。如果是这样，那是框架的错，不能很好地解析ctes(不是闻所未闻(。