CODEWITHSUNDEEP
sqloracle11g
jb3 Hd
jb3 Hd

Reputation: 155

Using the CREATE MATERIALIZED VIEW clause in oracle 11g

CREATE OR REPLACE MATERIALIZED VIEW MV_NAME BUILD IMMEDIATE AS
<SQL Query>

So I followed this example in my lecture. But it doesn't work when I run it in oracle. When I typed it in notepad++ with the file saved as .sql, the MATERIALIZED and BUILD parts didn't come out as bold, blue font like it should in Notepad++. I did some research online and I found that it all says the statement I am using is correct. But the SQL command line and Notepad++ say otherwise. I got ORA-00922: Missing or invalid option error in the prompt. Anyone know what the problem is and how to fix it?

Upvotes: 1

Views: 2571

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

The create materialized view statement doesn't support the or replace option that you can use with a normal view.

CREATE MATERIALIZED VIEW [ schema. ] materialized_view
  [ OF [ schema. ] object_type ]
  [ ( { scoped_table_ref_constraint
      | column_alias [ENCRYPT [encryption_spec]]
      }
      [, { scoped_table_ref_constraint
         | column_alias [ENCRYPT [encryption_spec]]
         }
      ]...
    )
  ]
  { ON PREBUILT TABLE
    [ { WITH | WITHOUT } REDUCED PRECISION ]
  | physical_properties materialized_view_props
  }
  [ USING INDEX
    [ physical_attributes_clause
    | TABLESPACE tablespace
    ]...
  | USING NO INDEX
  ]
  [ create_mv_refresh ]
  [ FOR UPDATE ]
  [ { DISABLE | ENABLE } QUERY REWRITE ]
AS subquery ;

If the MV already exists you need to drop it before creating it again, without that invalid clause.

DROP MATERIALIZED VIEW MV_NAME;
CREATE MATERIALIZED VIEW MV_NAME BUILD IMMEDIATE AS
<SQL Query>

Upvotes: 3

Related Questions