Skip to Main Content
  • Questions
  • Strange dependency in user_dependency: view depends on unreferenced function

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Muhammad Riaz.

Asked: February 20, 2018 - 11:08 am UTC

Last updated: February 21, 2018 - 11:07 am UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

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.

and Chris said...

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

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Muhammad Riaz Shahid, February 21, 2018 - 5:23 am UTC

Hi Chris,

Thanks a lot for answer.

My confusion is that the view is not selecting the column on which we have function-based index, still the dependency on the function is created. Also if we check the dependencies of the table, it doesn't have any for the function.

Regards,
Chris Saxon
February 21, 2018 - 11:07 am UTC

Yeah, functions used in virtual columns don't show up *_dependencies sadly...

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database