PLScope – powerful code analysis tool for PL/SQL Program units – first
introduced in Oracle 11g for tracking dependencies, usage, lack of usage and
violations of naming conventions. Now with Oracle 12c Release 2 (12.2), that
analysis includes the SQL statements inside your PL/SQL code base. This
significant enhancement enables developers to perform comprehensive impact
analysis of changes to tables.
PL/Scope gathers at compile time metadata about your PL/SQL code and
stores it in dedicated tables. These metadata are accessible for analysis via
the views dba/all/user_identfiers (since 11.1) and in dba/all/user_statements
(since 12.2)
Now let’s see how to use this feature to track the column level
dependencies in PL/SQL Program units.
For demo we consider these two packages (PKG, PKG2) in place.
demo@PDB1>
create or replace package PKG is
2
type t is table of number;
3
g1 int;
4
function F return number;
5
procedure P(p in number,
6
q in out scott.emp.deptno%type);
7
cursor c is select * from dual;
8 end;
9 /
Package
created.
demo@PDB1>
create or replace package body PKG is
2
type t is table of number;
3 g
int;
4
5
function F return number is
6
begin
7
null;
8
null;
9
null;
10
return 1;
11
end;
12
13
procedure P(p in number,
14 q in out
scott.emp.deptno%type) is
15
begin
16
for i in ( select * from scott.emp )
17
loop
18 null;
19
end loop;
20
21
select deptno into q from scott.emp;
22
end;
23
24
begin
25
select max(deptno)
26
into g
27
from scott.emp;
28 end;
29 /
Package body
created.
demo@PDB1>
create or replace package PKG2 is
2
type t is table of number;
3
g1 int;
4
function F1 return number;
5
6
procedure P2(p in number,
7 q in out
scott.emp.deptno%type);
8
cursor c is select * from dual;
9 end;
10 /
Package
created.
demo@PDB1>
create or replace package body PKG2 is
2
type t is table of number;
3 g
int;
4
5
function F1
6
return number is
7
begin
8
null;
9
null;
10
null;
11
return 1;
12
end;
13
14
procedure P2(p in number,
15 q in out
scott.emp.deptno%type) is
16
begin
17
for i in ( select * from scott.emp )
18
loop
19 null;
20
end loop;
21
22
select deptno into q from scott.emp;
23
end;
24
25
begin
26
select max(deptno)
27
into g
28
from scott.emp;
29 end;
30 /
Package body
created.
demo@PDB1>
The requirement is to get all those PL/SQL units dependent on the
column DEPTNO from the table SCOTT.EMP
First we need to recompile these packages with PLScope enabled (can be
done either at statement/session/instance/database level)
demo@PDB1>
alter package pkg compile plscope_settings='identifiers:all,statements:all';
Package
altered.
demo@PDB1>
alter package pkg compile body
plscope_settings='identifiers:all,statements:all';
Package body
altered.
demo@PDB1>
alter package pkg2 compile plscope_settings='identifiers:all,statements:all';
Package
altered.
demo@PDB1>
alter package pkg2 compile body plscope_settings='identifiers:all,statements:all';
Package body
altered.
demo@PDB1>
Now the details about these packages will be loaded into
ALL_IDENTIFIERS and ALL_STATEMENTS static data dictionary views. The first
thing we need to find is the signature (this is an identifiers for the
statement, it is unique across all program units) of the DEPTNO column from
SCOTT.EMP table
demo@PDB1>
select usage,signature
2 from
all_identifiers
3
where object_name ='EMP'
4 and
owner ='SCOTT'
5 and
name ='DEPTNO';
USAGE SIGNATURE
---------------
-----------------------------------
DECLARATION 8F77500971F46AAFC9C714D16498A00A
Since we are looking for all occurrence of a specific identifier, we
are working with SIGNATURE column – PLScope assigns a unique identifier to each
distinct identifier in our code base, that’s what it is stored in SIGNATURE
column.
Then we need to find all the references to this signature – that give
us all the references to the DEPTNO column from SCOTT.EMP table.
demo@PDB1>
select t1.object_name,t1.object_type,t1.usage, t2.text,t2.routine
2 from
all_identifiers t1 ,
3 (
select owner,name,type,line,text,
4
last_value( txt2 ignore nulls)
5
over( partition by name,type order by line) as routine
6 from (
7
select owner,name,type,line,text,
8 case
when line = 1 then name
9
when lower(trim(text)) like 'function%'
10
then regexp_substr( lower(trim(text)) ,'[[:alnum:]]+',9,1)
11 when
lower(trim(text)) like 'procedure%'
12
then regexp_substr( lower(trim(text)) ,'[[:alnum:]]+',11,1)
13 end
txt2
14 from
all_source
15
where name in ('PKG','PKG2')
16 and
owner = user
17 )
) t2
18
where t1.signature =( select s1.signature
19 from
all_identifiers s1
20 where
owner = 'SCOTT'
21 and type ='COLUMN'
22 and
object_name = 'EMP'
23 and
object_type ='TABLE'
24 and name =
'DEPTNO' )
25 and
t1.object_name = t2.name(+)
26 and
t1.object_type = t2.type (+)
27 and
t1.owner = t2.owner (+)
28 and
t1.line = t2.line(+)
29
order by 1,4 ;
OBJEC
OBJECT_TYPE USAGE TEXT ROUTI
-----
-------------- -------------- ------------------------------------- -----
EMP TABLE DECLARATION
PKG PACKAGE BODY REFERENCE q in out scott.emp.deptno%type) is p
PKG PACKAGE REFERENCE q in out scott.emp.deptno%type); p
PKG PACKAGE BODY REFERENCE select deptno into q from scott.emp; p
PKG PACKAGE BODY REFERENCE select max(deptno) p
PKG2 PACKAGE BODY
REFERENCE q in out
scott.emp.deptno%type) is p2
PKG2 PACKAGE
REFERENCE q in out
scott.emp.deptno%type); p2
PKG2 PACKAGE BODY
REFERENCE select deptno into
q from scott.emp; p2
PKG2 PACKAGE BODY
REFERENCE select
max(deptno) p2
9 rows
selected.
demo@PDB1>
However one would argue that we could get the above details from
scanning all_source dictionary. Something like this.
demo@PDB1>
with t as
2 (
select
3
case
4
when line = 1 then name
5
when ltrim(lower(text)) like 'function%'
6 then
regexp_substr(substr(ltrim(text),10),'[[:alnum:]]+')
7
when ltrim(lower(text)) like 'procedure%'
8 then
regexp_substr(substr(ltrim(text),11),'[[:alnum:]]+')
9
end tag,
10
u.*
11
from user_source u where name in ( 'PKG','PKG2')
12 )
13
select *
14
from (
15
select last_value(tag ignore nulls)
16 over ( partition by name,
type
17 order by line ) as
routine,
18
name,
19
type,
20 text
21
from t
22 )
23
where lower(text) like
'%deptno%';
ROUTI NAME TYPE TEXT
----- -------
------------- ----------------------------------------
PKG PKG
PACKAGE q in out
scott.emp.deptno%type);
PKG2 PKG2
PACKAGE q in out
scott.emp.deptno%type);
PKG PKG
PACKAGE BODY q in out
scott.emp.deptno%type) is
PKG PKG
PACKAGE BODY select deptno into
q from scott.emp;
PKG PKG
PACKAGE BODY select max(deptno)
PKG2 PKG2
PACKAGE BODY q in out
scott.emp.deptno%type) is
PKG2 PKG2
PACKAGE BODY select deptno into
q from scott.emp;
PKG2 PKG2
PACKAGE BODY select max(deptno)
8 rows
selected.
demo@PDB1>
Unfortunately, scanning all_source to get those dependencies list, can
return all kinds of false positives.
·
That phrase showing up inside a comment
·
That phrase as part of a large identifiers –
such as GET_DEPTNO
·
Column with same name but in present in other
tables (such as EMP table having DEPTNO columns)
let’s say in this below package body procedure P3 was added with
comments /* scott.deptno */
demo@PDB1>
create or replace package body PKG2 is
2
type t is table of number;
3 g
int;
4
5
function F1
6
return number is
7
begin
8
null;
9
null;
10
null;
11
return 1;
12
end;
13
14
procedure P2(p in number,
15 q in out
scott.emp.deptno%type) is
16
begin
17
for i in ( select * from scott.emp )
18
loop
19 null;
20
end loop;
21
22
select deptno into q from scott.emp;
23
end;
24
25
procedure p3 as
26
begin
27
for x in (select * from dual /* scott.deptno */ )
28
loop
29 null;
30
end loop;
31
end;
32
33
begin
34
select max(deptno)
35
into g
36
from scott.emp;
37 end;
38 /
Package body
created.
demo@PDB1>
alter package pkg2 compile body
2
plscope_settings='identifiers:all,statements:all';
Package body
altered.
demo@PDB1>
Without PLScope – scanning through all_source data dictionary can
return - false positives were possible. Like this.
demo@PDB1>
with t as
2 (
select
3
case
4
when line = 1 then name
5
when ltrim(lower(text)) like 'function%'
6 then
regexp_substr(substr(ltrim(text),10),'[[:alnum:]]+')
7
when ltrim(lower(text)) like 'procedure%'
8 then
regexp_substr(substr(ltrim(text),11),'[[:alnum:]]+')
9
end tag,
10
u.*
11
from user_source u where name in ( 'PKG','PKG2')
12 )
13
select *
14
from (
15
select last_value(tag ignore nulls)
16 over ( partition by name,
type
17 order by line ) as
routine,
18
name,
19
type,
20
text
21
from t
22 )
23
where lower(text) like '%deptno%';
ROUTI NAME TYPE TEXT
----- -------
-------------------- ----------------------------------------
PKG PKG
PACKAGE q in out
scott.emp.deptno%type);
PKG2 PKG2
PACKAGE q in out
scott.emp.deptno%type);
PKG PKG
PACKAGE BODY q in out
scott.emp.deptno%type) is
PKG PKG
PACKAGE BODY select deptno
into q from scott.emp;
PKG PKG
PACKAGE BODY select
max(deptno)
PKG2 PKG2
PACKAGE BODY q in out
scott.emp.deptno%type) is
PKG2 PKG2
PACKAGE BODY select deptno
into q from scott.emp;
PKG2 PKG2
PACKAGE BODY for x in
(select * from dual /* scott.deptno */ )
PKG2 PKG2
PACKAGE BODY select
max(deptno)
9 rows
selected.
However with PLScope features, these kind of false positives will never
be reported.
demo@PDB1>
select t1.object_name,t1.object_type,t1.usage, t2.text,t2.routine
2 from
all_identifiers t1 ,
3 (
select owner,name,type,line,text,
4
last_value( txt2 ignore nulls)
5
over( partition by name,type order by line) as routine
6 from (
7
select owner,name,type,line,text,
8 case
when line = 1 then name
9
when lower(trim(text)) like 'function%'
10
then regexp_substr(
lower(trim(text)) ,'[[:alnum:]]+',9,1)
11
when lower(trim(text)) like 'procedure%'
12
then regexp_substr( lower(trim(text)) ,'[[:alnum:]]+',11,1)
13 end
txt2
14 from
all_source
15
where name in ('PKG','PKG2')
16 and
owner = user
17 )
) t2
18
where t1.signature =( select s1.signature
19 from
all_identifiers s1
20 where
owner = 'SCOTT'
21 and type
='COLUMN'
22 and
object_name = 'EMP'
23 and
object_type ='TABLE'
24 and name =
'DEPTNO' )
25 and
t1.object_name = t2.name(+)
26 and
t1.object_type = t2.type (+)
27 and
t1.owner = t2.owner (+)
28 and
t1.line = t2.line(+)
29
order by 1,4 ;
OBJEC
OBJECT_TYPE USAGE TEXT ROUTI
-----
-------------- -------------- ------------------------------------- -----
EMP TABLE DECLARATION
PKG PACKAGE BODY REFERENCE q in out scott.emp.deptno%type) is p
PKG PACKAGE REFERENCE q in out scott.emp.deptno%type); p
PKG PACKAGE BODY REFERENCE select deptno into q from scott.emp; p
PKG PACKAGE BODY REFERENCE select max(deptno) p
PKG2 PACKAGE BODY
REFERENCE q in out
scott.emp.deptno%type) is p2
PKG2 PACKAGE
REFERENCE q in out
scott.emp.deptno%type); p2
PKG2 PACKAGE BODY
REFERENCE select deptno into
q from scott.emp; p2
PKG2 PACKAGE BODY
REFERENCE select
max(deptno) p3
9 rows
selected.
demo@PDB1>
No comments:
Post a Comment