时间戳

首页 > 解决方案 > 用于创建物化视图的 Oracle DDL 错误 PLS-00103

oracle - 用于创建物化视图的 Oracle DDL 错误 PLS-00103

问题描述

我通过 TOAD 在 Oracle 13.0 中的存储过程中有以下 Oracle DDL 代码：

--BUILD AND POPULATE MATERIALIZED VIEWS 
BEGIN
 EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW WORK.Work1_MV';
  EXCEPTION
     WHEN OTHERS
     THEN NULL;
END;

--Create Materialized View (PLS-00103 Error)*
 CREATE MATERIALIZED VIEW WORK.Work1_MV

    NOLOGGING
    BUILD DEFERRED
 AS
    SELECT *
      FROM WORK.WorkA_V
      ;

 BEGIN 
   DBMS_MVIEW.REFRESH ('WORK.Work1_MV', 'C', ATOMIC_REFRESH  => FALSE);
 END;
 COMMIT;

--Create Index on Materialized View (PLS-00103 Error)*

CREATE BITMAP INDEX WORK.Work1_MV_MAP1 ON WORK.Work1_MV
 (ELEMENT_NAME)
 NOLOGGING
 COMPUTE STATISTICS;

--Create 2nd Index on Materialized View (PLS-00103 Error)*

CREATE BITMAP INDEX WORK.Work1_MV_MAP2 ON WORK.Work1 MV
 (MAP_ID)
 NOLOGGING
 COMPUTE STATISTICS;

当以上分别运行时，它们似乎可以工作。但是，虽然嵌入在存储过程中；他们无法使用与我在上述代码中注释的部分相关的 PLS-00103 错误进行编译。

完整的错误信息如下：

“[错误] PLS-00103 (329: 5): PLS-00103: 在预期以下情况之一时遇到符号“CREATE”: (begin case declare end exception exit for goto if loop mod null pragma raise return select update while with )"

如果有人可以分享正确编译这些语句的解决方案，我将不胜感激。

谢谢你。

标签： oraclestored-proceduresplsqlddl

解决方案

您不能DDL直接在PL/SQL块中使用任何。您必须在动态 SQL 中使用EXECUTE IMMEDIATE. 我为您创建了它，如下所示：

SQL> CREATE OR REPLACE PROCEDURE YEN_CHING_PROC AS
  2  --BUILD AND POPULATE MATERIALIZED VIEWS
  3  BEGIN
  4      BEGIN
  5          EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW WORK.Work1_MV';
  6      EXCEPTION
  7          WHEN OTHERS THEN
  8              NULL;
  9      END;
 10
 11  --Create Materialized View (PLS-00103 Error)*
 12      EXECUTE IMMEDIATE 'CREATE MATERIALIZED VIEW WORK.Work1_MV
 13
 14      NOLOGGING
 15      BUILD DEFERRED
 16   AS
 17      SELECT *
 18        FROM WORK.WorkA_V
 19        '
 20      ;
 21      --BEGIN
 22          DBMS_MVIEW.REFRESH('WORK.Work1_MV', 'C', ATOMIC_REFRESH => FALSE);
 23      --END;
 24      COMMIT;
 25
 26  --Create Index on Materialized View (PLS-00103 Error)*
 27      EXECUTE IMMEDIATE 'CREATE BITMAP INDEX WORK.Work1_MV_MAP1 ON WORK.Work1_MV
 28   (ELEMENT_NAME)
 29   NOLOGGING
 30   COMPUTE STATISTICS'
 31      ;
 32
 33  --Create 2nd Index on Materialized View (PLS-00103 Error)*
 34      EXECUTE IMMEDIATE 'CREATE BITMAP INDEX WORK.Work1_MV_MAP2 ON WORK.Work1 MV
 35   (MAP_ID)
 36   NOLOGGING
 37   COMPUTE STATISTICS';
 38
 39  END YEN_CHING_PROC;
 40  /

Procedure created.

SQL>

干杯！！

推荐阅读