1

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?

René Nyffenegger
  • 3,763
  • 7
  • 32
  • 39

0 Answers0