Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Materialized views and number column problem : shape of prebuilt table does not match definition query (ORA-12060)
This is related to how ORACLE handles NUMBER type casting in a view.
If you try to specify precision for a NUMBER in your view definition,
you always end up losing the precision. Not sure if this is considered
a bug. In any case, if you just use NUMBER without the precision, it
would work.
SQL> desc test3
Name Null? Type ----------------------------------------- -------- ---------------------------- C1 NOT NULL VARCHAR2(2) C2 NUMBER(1) C3 NUMBER(2)
SQL> create view test3_vw(col1, col2) as select cast(c1 as
varchar2(3)), cast(c2 as number(3)) from
test3
2 ;
View created.
Name Null? Type ----------------------------------------- -------- ---------------------------- COL1 VARCHAR2(3) COL2 NUMBER
SQL> create materialized view tyu
2 on prebuilt table as
3 select cast(c1 as varchar2(3)) col1, cast(c2 as number) col2
from test3;
select cast(c1 as varchar2(3)) col1, cast(c2 as number) col2 from
test3
*ERROR at line 3:
SQL> drop materialized view tyu;
Materialized view dropped.
SQL> create materialized view tyu
2 on prebuilt table as
3 select cast(c1 as varchar2(3)) col1, cast(c2 as number) col2
from test3;
Materialized view created.
SQL> desc tyu
Name Null? Type ----------------------------------------- -------- ---------------------------- COL1 VARCHAR2(3) COL2 NUMBER
SQL>
"Antoine Bonavita" <abonavita2003_at_hotmail.com> wrote in message news:<F4yta.977$FA3.156_at_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 - 18:05:02 CDT