plscope_tab_usage does not always handle synonyms consistently

[rvo-cs]

Test setup:

alter session set current_schema = "SCOTT";
alter session set plscope_settings = "IDENTIFIERS:ALL, STATEMENTS:ALL";

create table t1 (c1 number);
create synonym s_t1 for t1;
create view vw_t1 as select * from s_t1;
create synonym s_vw_t1 for vw_t1;

create table t2 (c1 number);
create synonym sa_t2 for t2;
create synonym sb_t2 for sa_t2;
create view vw_t2 as select * from sb_t2;
create synonym sa_vw_t2 for vw_t2;
create synonym sb_vw_t2 for sa_vw_t2;

create package pkg_tab_usage_tst as
   procedure proc;
end pkg_tab_usage_tst;
/

create package body pkg_tab_usage_tst as
   procedure proc 
   is
   begin
      insert into s_t1(c1)
      select * from s_vw_t1;
      insert into sb_t2(c1)
      select * from sb_vw_t2;
   end proc;
end pkg_tab_usage_tst;
/

We have created a table t1, a matching view vw_t1, and private synonyms for both objects; the chain of dependencies is as follows: s_vw_t1 (synonym) --> vw_t1 (view) --> s_t1 (synonym) --> t1

Likewise, we have created a table t2, a matching view vw_t2, and private synonyms for both, but this time we have not used a single synonym for each object, but a chain of 2 synonyms: 1 synonym for the base object, and 1 synonym for the synonym. The resulting chain of dependencies is as follows: sb_vw_t2 (synonym) --> sa_vw_t2 (synonym) --> vw_t2 (view) --> sb_t2 (synonym) --> sa_t2 (synonym) --> t2

Remark: this is arguably a made-up test case: chains of synonyms are rarely seen in the wild, but they may exist nonetheless.

Test query:

exec plscope_context.set_attr('OWNER', 'SCOTT');
exec plscope_context.set_attr('OBJECT_NAME', 'PKG_TAB_USAGE_TST');

select line,
       col,
       procedure_name,
       operation,
       ref_object_type,
       ref_object_name,
       direct_dependency,
       text
  from plscope.plscope_tab_usage
 order by line,
       col,
       ref_object_type,
       ref_object_name;

Results:

LINE    COL    PROCEDURE_NAME    OPERATION    REF_OBJECT_TYPE    REF_OBJECT_NAME    DIRECT_DEPENDENCY    TEXT                             
      5     19 PROC              INSERT       SYNONYM            S_T1               YES                        insert into s_t1(c1)       
      5     19 PROC              INSERT       TABLE              T1                 NO                         insert into s_t1(c1)       
      6     21 PROC              INSERT       SYNONYM            S_VW_T1            YES                        select * from s_vw_t1;     
      6     21 PROC              INSERT       TABLE              T1                 NO                         select * from s_vw_t1;     
      6     21 PROC              INSERT       VIEW               VW_T1              NO                         select * from s_vw_t1;     
      7     19 PROC              INSERT       TABLE              T2                 NO                         insert into sb_t2(c1)      
      8     21 PROC              INSERT       TABLE              T2                 NO                         select * from sb_vw_t2;    
      8     21 PROC              INSERT       VIEW               VW_T2              NO                         select * from sb_vw_t2;    

Comment: all usages of tables and views are found as expected. However, there's no mention of any of the intervening synonyms at lines 7 and 8, and this is not consistent with usages of synonyms S_T1 and S_VW_T1 being reported, at lines 5 and 6 respectively.

Expected results: it's hard to know exactly what should be expected without a formal specification of the plscope_tab_usage view in the first place. Meanwhile, it would make sense that every synonym directly referenced in the source code (DIRECT_DEPENDENCY is YES) and leading to a table or a view, either directly or through a chain of synonyms, be returned.

E.g.:

LINE    COL    PROCEDURE_NAME    OPERATION    REF_OBJECT_TYPE    REF_OBJECT_NAME    DIRECT_DEPENDENCY    TEXT                             
      5     19 PROC              INSERT       SYNONYM            S_T1               YES                        insert into s_t1(c1)       
      5     19 PROC              INSERT       TABLE              T1                 NO                         insert into s_t1(c1)       
      6     21 PROC              INSERT       SYNONYM            S_VW_T1            YES                        select * from s_vw_t1;     
      6     21 PROC              INSERT       TABLE              T1                 NO                         select * from s_vw_t1;     
      6     21 PROC              INSERT       VIEW               VW_T1              NO                         select * from s_vw_t1;     
      7     19 PROC              INSERT       SYNONYM            SB_T2              YES                        insert into sb_t2(c1)      
      7     19 PROC              INSERT       TABLE              T2                 NO                         insert into sb_t2(c1)      
      8     21 PROC              INSERT       SYNONYM            SB_VW_T2           YES                        select * from sb_vw_t2;    
      8     21 PROC              INSERT       TABLE              T2                 NO                         select * from sb_vw_t2;    
      8     21 PROC              INSERT       VIEW               VW_T2              NO                         select * from sb_vw_t2;

The intermediary synonyms sa_t2 and sa_vw_t2 would not be returned, as they are neither directly referenced in the PKG_TAB_USAGE_TST package body, nor are they "final" table or view objects that we are primarily interested in.

[PhilippSalvisberg]

Thanks for reporting this issue. It looks like a bug.