Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: A question about materialized view

Re: A question about materialized view

From: Gaetan Poitras <poitrasg_at_crcsogema.com>
Date: 10 Jun 2002 12:50:41 -0700
Message-ID: <3ebc2be2.0206101150.41f1e362@posting.google.com>


violin.hsiao_at_mail.pouchen.com.tw (Violin) wrote in message news:<d22954a4.0206100227.3e99b7b1_at_posting.google.com>...
> 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

You did a read-only snapshot instead of a updatable snapshot.. Received on Mon Jun 10 2002 - 14:50:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US