Oracle DDL Error PLS-00103 for Creation of Materialized View

Question

I have the following Oracle DDL code within a stored procedure in Oracle 13.0 through TOAD:

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

When the above are ran separately, they seem to work. However, while embedded in a stored procedure; they fail to compile with the PLS-00103 errors pertaining to the sections I have commented in the above code.

The complete error message is below:

"[ERROR] PLS-00103 (329: 5): PLS-00103: Encountered the symbol "CREATE" when expecting one of the following: (begin case declare end exception exit for goto if loop mod null pragma raise return select update while with)"

I would greatly appreciate if anyone can share solutions to compile these statements correctly.

Thank you.

You can't have DDL directly in a stored procedure. Each DDL statement would have to be a dynamic SQL statement (i.e. use execute immediate for each statement). Are you sure that you want bitmap indexes? And are you sure that you want to drop and re-create the materialized view every time rather than just refreshing it? — Justin Cave
Hi Justin, I'm just wondering are there better ways to create indexes other than bitmap? Apologies, this is a bit new to me. — Yen Ching

Popeye Popeye · Accepted Answer · 2019-11-19T15:03:09

You can not use any DDL directly in the PL/SQL block. You have to use it in dynamic SQL using EXECUTE IMMEDIATE. I have created it for you as follows:

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>

Cheers!!

Oracle DDL Error PLS-00103 for Creation of Materialized View

2 Answers