Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!news-feed01.roc.ny.frontiernet.net!nntp.frontiernet.net!logbridge.uoregon.edu!newsfeed.stanford.edu!postnews1.google.com!not-for-mail From: JusungYang@yahoo.com (Jusung Yang) Newsgroups: comp.databases.oracle.misc Subject: Re: Materialized views and number column problem : shape of prebuilt table does not match definition query (ORA-12060) Date: 5 May 2003 16:05:02 -0700 Organization: http://groups.google.com/ Lines: 135 Message-ID: <130ba93a.0305051505.5fef607d@posting.google.com> References: NNTP-Posting-Host: 64.165.201.215 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1052175902 31619 127.0.0.1 (5 May 2003 23:05:02 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 5 May 2003 23:05:02 GMT Xref: core-easynews comp.databases.oracle.misc:97349 X-Received-Date: Mon, 05 May 2003 16:04:32 MST (news.easynews.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" wrote in message news:... > 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 identified by > using ; > > 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@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@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@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@hotmail.com)