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.

On Apr 14, 10:27*am, The Magnet <a... (AT) unsu (DOT) com> wrote:
It looks like you are trying to create an object type materialized
view which would require use of the OF OBJECT_TYPE clause.

See >> OF object_type

The OF object_type clause lets you explicitly create an object
materialized view of type object_type.
<<

at http://download.oracle.com/docs/cd/B...htm#SQLRF01302

HTH -- Mark D Powell --

On Apr 15, 11:43*am, Mark D Powell <Mark.Powe... (AT) hp (DOT) com> wrote:
Hi Mark,

Saw the previously and tried it with no luck. So, I'm still
researching on what it may be. Bottom line is that I should be able
to create a materialized from anything I want. Source of the data
should not matter, since a materialized view is the product.........