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

Question

我在Oracle 13.0到TOAD的存储过程中有以下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"：(如果循环mod null pragma raise return select update while with(">，则goto的begin case声明结束异常退出

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

谢谢。

Answer 1

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

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>

干杯！！

Answer 2

除非对表示DDL的字符串变量使用EXECUTE IMMEDIATE，否则无法在PL/SQL块中执行DDL。