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.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent