Can anyone share me some light on why my Oracle SQL Developer flagged my view as broken while it is actually working?
It is not just 1 view, but 10 views have the same problem, they are all created in a different time, with subquery or joining to multiple tables and is always working fine.
Thanks in advance!
Advertisement
Answer
Probably the view was invalidated by a change to a referenced object. Like stored PL/SQL, a recompilation will occur when it is next used, when it will become valid again. For example:
SQL> create table demo (id integer); Table created SQL> create or replace view v1 as select id from demo; View created SQL> select o.status from user_objects o where object_type = 'VIEW' and object_name = 'V1'; STATUS ------- VALID SQL> alter table demo modify id varchar2(10); Table altered SQL> select o.status from user_objects o where object_type = 'VIEW' and object_name = 'V1'; STATUS ------- INVALID SQL> select * from v1; ID ---------- SQL> select o.status from user_objects o where object_type = 'VIEW' and object_name = 'V1'; STATUS ------- VALID