Materialized views and number column problem : shape of prebuilt table does not match definition query (ORA-12060)

From: Antoine Bonavita <abonavita2003_at_hotmail.com>
Date: Mon, 05 May 2003 18:29:57 GMT
Message-ID: <F4yta.977$FA3.156@newssvr16.news.prodigy.com>

I'm trying to set up a replication between two tables which are slightly different. It's a simple read-only configuration on the materialized view site with only one master site.

On the master database, I have:
- A table foo created as follow:

  create table foo (foo_id number(38) primary key, foo_name varchar2(80));
- A materialized view log for table foo, created by:
  create materialized view log on foo;

On the slave database, I have:
- A table bar created as follow:

  create table bar (bar_id number(38) primary key, bar_name varchar2(100), bar_enabled number(1) not null);
- A dblink to the master database:

  create database link master connect to <user> identified by <password> using <connect_string>;

When I try to make bar a materialized view of foo with:   create materialized view bar
  on prebuilt table refresh fast as
  select foo_id bar_id,

           foo_name bar_name,
          1 bar_enabled

  from foo_at_master;
It fails (expectedly) with:

       foo_name bar_name,
       *

ERROR at line 4:
ORA-12060: shape of prebuilt table does not match definition query

This, I can fix with a cast:
  create materialized view bar
  on prebuilt table refresh fast as
  select foo_id bar_id,

           cast(foo_name as varchar2(100)) bar_name,
          1 bar_enabled

  from foo_at_master;
But it still fails with:

          1 bar_enabled
          *

ERROR at line 5:
ORA-12060: shape of prebuilt table does not match definition query

Of course, I tried the cast thing again:   create materialized view bar
  on prebuilt table refresh fast as
  select foo_id bar_id,

           cast(foo_name as varchar2(100)) bar_name,
           cast(1 as number(1)) bar_enabled

  from foo_at_master;
It does not get me any further:

           cast(1 as number(1)) bar_enabled
           *

ERROR at line 5:
ORA-12060: shape of prebuilt table does not match definition query

I don't understand why it works with varchar2 but not with number. And I would very much appreciate any help with this.

Thanks.

Antoine Bonavita (abonavita2003_at_hotmail.com) Received on Mon May 05 2003 - 13:29:57 CDT