Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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)

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

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 5 May 2003 16:05:02 -0700
Message-ID: <130ba93a.0305051505.5fef607d@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.

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>

"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US