Re: Insert into Materialized View

amerar_at_iwc.net schrieb:
> Hi,
>
> We're pretty new at using Materialized Views, so.......
>
> We have a materialized view: SPECIAL_TST. It seems that the base
> table also has the same name. So, when I try and insert, I get an
> ORA-01732.
>
> First I guess I want to know where the source is which made the
> materialized view, which dictionary table? Next, how to insert my
> values. Do I need to drop the view and re-create it?
>
> Thanks.
>

You should have a look on documentation about read only materialized views, updateable materialized views and writeable materialized views.

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14226/repmview.htm#sthref475

You cannot manipulate read only materialized views and by writeable materialized views changes will disappear after refresh.

SQL> select * from dept;

     DEPTNO DNAME                                      LOC
---------- ------------------------------------------
---------------------------------------
         10 ACCOUNTING                                 NEW YORK
         20 RESEARCH                                   DALLAS
         30 SALES                                      CHICAGO
         40 OPERATIONS                                 BOSTON

SQL> create materialized view dept_mv as select * from dept;

Materialized view created.

SQL> select * from dept_mv;

     DEPTNO DNAME                                      LOC
---------- ------------------------------------------
---------------------------------------
         10 ACCOUNTING                                 NEW YORK
         20 RESEARCH                                   DALLAS
         30 SALES                                      CHICAGO
         40 OPERATIONS                                 BOSTON

SQL> insert into dept_mv values(50,'TESTING','MUNICH'); insert into dept_mv values(50,'TESTING','MUNICH')

             *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

SQL> drop materialized view dept_mv;

Materialized view dropped.

SQL> create materialized view dept_mv FOR UPDATE as select * from dept;

Materialized view created.

SQL> select * from dept_mv;

     DEPTNO DNAME                                      LOC
---------- ------------------------------------------
---------------------------------------
         10 ACCOUNTING                                 NEW YORK
         20 RESEARCH                                   DALLAS
         30 SALES                                      CHICAGO
         40 OPERATIONS                                 BOSTON

SQL> insert into dept_mv values(50,'TESTING','MUNICH');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept_mv;

     DEPTNO DNAME                                      LOC
---------- ------------------------------------------
---------------------------------------
         10 ACCOUNTING                                 NEW YORK
         20 RESEARCH                                   DALLAS
         30 SALES                                      CHICAGO
         40 OPERATIONS                                 BOSTON
         50 TESTING                                    MUNICH

SQL> exec dbms_mview.refresh('DEPT_MV');

PL/SQL procedure successfully completed.

SQL> select * from dept_mv;

     DEPTNO DNAME                                      LOC
---------- ------------------------------------------
---------------------------------------
         10 ACCOUNTING                                 NEW YORK
         20 RESEARCH                                   DALLAS
         30 SALES                                      CHICAGO
         40 OPERATIONS                                 BOSTON

SQL> Best regards

Maxim Received on Mon Dec 12 2005 - 09:47:06 CST