Re: View getting invalidated

Yash R. Ganthe wrote:
>
> Hi,
> I have 2 tables A and B. The schema of both is identical. A view V is
> defined as ( select * from A union all select * from B).
> If I drop a particular column from both A and B, the view V is left
> invalidated. The Oracle documentation also says so. Any view that depends on
> a table that has been altered, remains invalidated till it is recompiled.
>
> So I did a : alter view V compile.
> This command succeeded.But the view did not get validated.
>
> Can anyone guess why this happens? One would expect the query to be
> reevaluated after recompilation. Since there is a select *, it should work
> fine. But it doesn't. Can someone suggest something?
>
> Thanks
> Yash

select '*' gets transformed into the column names - you can see this from the definition in USER_VIEWS, thus the problem after dropping a column. Its best to explicitly list the columns in a view to avoid this.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."