Re: Materialized View
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Fri, 15 Apr 2011 09:43:52 -0700 (PDT)
Message-ID: <886e6001-8093-404c-bac5-f8df0a7d5446_at_t16g2000vbi.googlegroups.com>
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.
Date: Fri, 15 Apr 2011 09:43:52 -0700 (PDT)
Message-ID: <886e6001-8093-404c-bac5-f8df0a7d5446_at_t16g2000vbi.googlegroups.com>
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.
<<
at http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm#SQLRF01302
HTH -- Mark D Powell -- Received on Fri Apr 15 2011 - 11:43:52 CDT