A question about materialized view

Hollo,

I've created a materialized view for testing: SQL> create materialized view test_mv refresh force   2 as
  3 select rownum rownumber

  4       , rt.name
  5       , rc.customer_number
  6       , rt.description
  7    from ra_customers_at_a400         rc
  8       , ar_customer_profiles_at_a400 acp
  9       , ra_terms_at_a400             rt
 10   where rc.customer_id = acp.customer_id
 11     and acp.standard_terms = rt.term_id;

Snapshot created.

SQL> alter materialized view test_mv add process_flag char(1);

Snapshot altered.

SQL> desc test_mv

 Name                            Null?    Type
 ------------------------------- -------- ----
 ROWNUMBER                                NUMBER
 NAME                            NOT NULL VARCHAR2(15)
 CUSTOMER_NUMBER                 NOT NULL VARCHAR2(30)
 DESCRIPTION                              VARCHAR2(240)
 PROCESS_FLAG                             VARCHAR2(1)

SQL> But errors when trying to update mv:

SQL> update test_mv set process_flag = 'N'; update test_mv set process_flag = 'N'

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

SQL> I think it's reasonable 'casue I'm trying to update a joined view. Why I do is for replication to none-Oracle database,

The source data is from my Oracle ERP application(server A) , I create materialized view at another database via db_link (server B). Auto-refresh runs normally between server A & B, And then another application (Server C) will get new data from server B ,
But it's not Oracle DB.

To avoid duplicate getting rows , I want to put a process_flag to know if server C get this row already.

But I can only add a column but cannot update the column's value.

Is it possible to update a complex mv? or impossible? or other solution?

Any tips are appreicate,
Thanks and Regards,

Violin.
violin.hsiao_at_mail.pouchen.com.tw Received on Mon Jun 10 2002 - 05:27:27 CDT