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:
x
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