creating snapshot without constraints

From: <amitabh.mehra_at_gmail.com>
Date: Fri, 21 Dec 2007 02:28:08 -0800 (PST)
Message-ID: <51fd1a15-ceb6-4b43-95fb-6d56ef7b93bd@d4g2000prg.googlegroups.com>

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. Received on Fri Dec 21 2007 - 04:28:08 CST