Re: creating snapshot without constraints

On Dec 21, 5:28 am, amitabh.me..._at_gmail.com wrote:
> Hi
> Consider a test table - mytable:
>                        id        number   pk
>                        name   varchar2(20) not null,
>                        addr     varchar2(100) not null default 'local'
>
> I populated this table with some 200 test records.
> when I am trying to create snapshot using the following:
>
>                          create snapshot mytable_s refresh force as
> select * from test table;
>
> all works well.
> But in my requirement, i have something like if addr = 'local' then it
> should be null in the snapshot.
>
> So i tried something like:
>
>                          create snapshot mytable_s refresh force as
> select
>
> id,
>
> name,
>
> decode(addr, 'local', null)
>
> from test table;
>
> this throws error:   ORA-01723: zero-length columns are not allowed
>
> Is creating a snapshot without having the constraints of the table
> possible? if yes, how? please dont ask me to remove the not null
> constraint from addr field. I cant do it.
>
> Thanks in advance.

Hello,

What version of Oracle are you currently running?

Regards,
Arun Received on Fri Dec 21 2007 - 08:23:13 CST