Materialized View

Hey, thanks to those who helped me with that convoluted query before. Here is another weird thing. I need to create a Materialized View based on a virtual table in my PL/SQL code. So, I define the type and define the record and assign values to it like this:

CREATE OR REPLACE
TYPE MF_RECORD_TYPE AS OBJECT
.
.

CREATE OR REPLACE
TYPE MF_TABLE_TYPE IS TABLE OF mf_record_type;
.
.

v_mf_record mf_table_type := mf_table_type();
.
.

v_mf_record(x) := mf_record_type(data,........

But the actual materialized view statement fails:

  EXECUTE IMMEDIATE ('CREATE MATERIALIZED VIEW mf_snapshot_mv AS     SELECT * FROM TABLE (CAST (v_mf_record AS mf_table_type))');

ORA-00904: "V_MF_RECORD": invalid identifier

Can I not do this to create a materialized view? In case it matters, we are on 10gR2. Received on Thu Apr 14 2011 - 09:27:04 CDT