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

Not really what I was hoping for. But at least I understand what is going on now. I just have to work around it.

Thank you very much for the enlightment.

Antoine.

JusungYang_at_yahoo.com (Jusung Yang) wrote in message news:<130ba93a.0305051505.5fef607d_at_posting.google.com>...
> 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.
>
> -- Note that the precision is lost
> SQL> desc test3_vw
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> COL1 VARCHAR2(3)
> COL2 NUMBER
>
> SQL> desc tyu
> 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:
> ORA-12006: a materialized view with the same user.name already exists
>
>
> 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>
>
>
> - Jusung Yang
>
>
> "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 Wed May 07 2003 - 17:30:48 CDT