When I create an (invalid) view that selects from another invalid view, the view does not immediately show up in user_dependencies. I am wondering if this is expected.
These are the steps to reproduce the behavior:
First, I create a table
create table tq84_t (
col_1 number,
col_2 number
);
Then I create a view that is dependent on the table. Note the typo col_22 instead of col_2:
create or replace force view tq84_v as select col_1, col_22 from tq84_t;
Of course, this view will be created in an invalid state:
Warning: View created with compilation errors.
I create another view which is dependent on tq84_v (which will also be in an invalid state):
create force view tq84_v2 as select * from tq84_v;
I now query user_dependencies to check whether I have objects that are dependent on tq84_v. I would have expected TQ84_V2 to be reported, but the following query returns no record:
select name from user_dependencies where referenced_name = 'TQ84_V';
I re-compile the second view:
alter view tq84_v2 compile;
and query user_dependencies again:
select name from user_dependencies where referenced_name = 'TQ84_V';
Only this time, Oracle will report TQ84_V2 as being dependent on TQ84_V.
Is this behavior expected?