PeopleSoft and Invalid Views in the Oracle Database

I was listening to the section on Invalid Views in PSADMIN Podcast #117 (@19:00). Essentially, when you drop and recreate a view that is referenced by a second view, the status on the second view in the database goes invalid. This is not a huge problem because as soon as you query the second view it is compiled. However, you would like to know whether any change to a view prevents any dependent views from compiling, although you would expect have teased these errors out before migration to production.
The PeopleSoft solution to this is to include all the dependent views in the Application Designer project. However, as pointed out, in the podcast you are now releasing code, possibly unintentionally releasing code changes and certainly updating last change dates on record definitions when really you just need to compile the database objects.   PeopleSoft does this because it is a platform generic solution, but really this is using the PeopleSoft Application Designer to solve a database management issue.
A similar problem also occurs in the Oracle database with dependent PL/SQL procedures and packages where you sometimes get referential loops. Oracle provides a procedure DBMS_UTILITY.COMPILE_SCHEMA that recompiles all invalid objects in a schema and reports any errors to the ALL_ERRORS view.  I think this is a much safer option.

Here is a very simple (non-PeopleSoft) example

drop table t purge;
drop view a;
drop view b;

create table t (a number);
insert into t values (1); 
create view a as select a from t; 
create view b as select a from a; 

column object_name format a12
select object_type, object_name, status 
from user_objects where object_name IN('T','A','B')
/

OBJECT_TYPE             OBJECT_NAME  STATUS
----------------------- ------------ -------
TABLE                   T            VALID
VIEW                    B            VALID
VIEW                    A            VALID

Dropping and recreating view A renders view B invalid.

drop view a;
create view a as select a from t; 

select object_type, object_name, status 
from user_objects
where object_name IN('T','A','B');

OBJECT_TYPE             OBJECT_NAME  STATUS
----------------------- ------------ -------
TABLE                   T            VALID
VIEW                    B            INVALID
VIEW                    A            VALID

Just querying B makes it valid again.

select * from b;
select object_type, object_name, status 
from user_objects where object_name IN('T','A','B');

OBJECT_TYPE             OBJECT_NAME  STATUS
----------------------- ------------ -------
TABLE                   T            VALID
VIEW                    B            VALID
VIEW                    A            VALID

Let's make B invalid again by rebuild A, but this time I will change the name of the column in view A from A to T so that view B cannot compile without an error.  I can recompile every invalid object in the schema by calling DBMS_UTILITY_COMPILE_SCHEMA.  However, B remains invalid because there is an error.

drop view a;
create view a (t) as select a from t;  

EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'SCOTT');
select object_type, object_name, status 
from user_objects where object_name IN('T','A','B');

OBJECT_TYPE             OBJECT_NAME  STATUS
----------------------- ------------ -------
TABLE                   T            VALID
VIEW                    B            INVALID
VIEW                    A            VALID

I can query the errors from USER_ERRORS.  So now I have recompiled all invalid objects and have a report of the exceptions that I can work on fixing.

NAME
---------------------------------------------------
TYPE           SEQUENCE       LINE   POSITION
------------ ---------- ---------- ----------
TEXT
---------------------------------------------------
ATTRIBUTE MESSAGE_NUMBER
--------- --------------
B
VIEW                  1          0          0
ORA-00904: "A": invalid identifier
ERROR                  0

N.B.: if you use CREATE OR REPLACE VIEW, then the view is not left invalid unless there is an error. Unfortunately, Application Designer always drops and recreates views.