Oracle user_dependency中的奇怪依赖: 视图依赖于未引用的函数

SQL> create table test_20 ( a number)
  2  /

Table created.

SQL> 
SQL> create or replace function test_function (p_1 in number)
  2  return number
  3  deterministic
  4  is
  5  begin
  6   return 1;
  7  end;
  8  /

Function created.

SQL> 
SQL> create index test_20_a on test_20(test_function(a))
  2  /

Index created.

SQL> 
SQL> 
SQL> create view vw_test_20 as select 1 as cnt from test_20
  2  /

View created.

SQL> select REFERENCED_NAME, REFERENCED_type, DEPENDENCY_TYPE
  2  from user_Dependencies where name='VW_TEST_20'
  3  /

REFERENCED_NAME                                                  REFERENCED_TYPE    DEPE
---------------------------------------------------------------- ------------------ ----
TEST_FUNCTION                                                    FUNCTION           HARD
TEST_20                                                          TABLE              HARD

复制

create table test_20 ( a number);

create or replace function test_function (p_1 in number)
return number
deterministic
is
begin
 return 1;
end;
/

create index test_20_a on test_20(test_function(a));
create view vw_test_20 as select 1 as cnt from test_20;

select column_name, data_default, hidden_column, virtual_column
from   user_tab_cols
where  table_name = 'TEST_20';

COLUMN_NAME    DATA_DEFAULT                   HIDDEN_COLUMN   VIRTUAL_COLUMN   
A                                       NO              NO               
SYS_NC00002$   "CHRIS"."TEST_FUNCTION"("A")   YES             YES   
复制

create or replace function test_function (p_1 in number)
return number
deterministic
is
begin
 return 1
end;
/
show err

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/1      PLS-00103: Encountered the symbol "END" when expecting one of the following:

select count(*) from test_20;

SQL Error: ORA-06575: Package or function TEST_FUNCTION is in an invalid state
复制

select count(*) from vw_test_20;

SQL Error: ORA-04063: view "CHRIS.VW_TEST_20" has errors
复制

create or replace function test_function (p_1 in number)
return number
deterministic
is
begin
 return 1;
end;
/
show err

No errors.

select count(*) from test_20;

COUNT(*)   
         0 

select count(*) from vw_test_20;

COUNT(*)   
         0 
复制