SQL & PL/SQL

Hello! First, how do you make your code formatted here?

Second, I need to join two tables into a materialized view. The view mast be fast refreshed on commit. Thus I do:

create materialized view log on d_city
WITH PRIMARY KEY, rowid;

create materialized view log on d_city_ml
WITH PRIMARY KEY, rowid;

create materialized view d_city_mw refresh fast on commit as
  select m.rowid as m_rowid,
         s.rowid as s_rowid,
         m.d_city_id,
         m.city_id,
         s.d_lang_id,
         m.cnt_id,
         m.city_code,
         m.city_name_eng,
         s.city_name,
         m.is_deleted
    from d_city m, d_city_ml s
   where m.d_city_id = s.d_city_id

I have to include rowid's, otherwise I get an error (ORA-12052).

However, this MV should be later replicated to another database with oracle streams. But Oracle Streams capture process doesn't support ROWID type. Is there a way to create fast refresh on commit materialized view without rowid's?

Or maybe there is a workaround?

Edited by: 922141 on 09.04.2012 7:18

Edited by: 922141 on 09.04.2012 23:10

Edited by: 922141 on 09.04.2012 23:11