Dear Team,
I will try to simplify the scenario we have, using a simple test case:
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
Why view has a dependency on TEST_FUNCTION?
Thanks.
When you create a function-based index, Oracle Database adds a hidden virtual column to "store" the result of this for each row:
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 <null> NO NO
SYS_NC00002$ "CHRIS"."TEST_FUNCTION"("A") YES YES
If you invalidate the function, you can no longer query the table:
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
Which of course means you can't query the view either:
select count(*) from vw_test_20;
SQL Error: ORA-04063: view "CHRIS.VW_TEST_20" has errors
The real problem here is the function. Fix it and everything's fine again:
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
Hence the dependency