Re: Materialized View

From: The Magnet <art_at_unsu.com>
Date: Mon, 18 Apr 2011 09:11:50 -0700 (PDT)
Message-ID: <c74f0194-c7d1-4249-9288-fe1ce7d61920_at_32g2000vbe.googlegroups.com>

On Apr 15, 11:43 am, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> On Apr 14, 10:27 am, The Magnet <a..._at_unsu.com> wrote:
>
>
>
>
>
>
>
>
>
> > 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.
>
> 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.
> <<
>
> athttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statem...
>
> HTH -- Mark D Powell --

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......... Received on Mon Apr 18 2011 - 11:11:50 CDT