PL/Scope is a compiler-driven tool that collects data about user-defined identifiers from PL/SQL source code at program-unit compilation time and makes it available in static data dictionary views. The collected data includes information about identifier types, usages (declaration, definition, reference, call, and assignment), and the location of each usage in the source code.
That is a fancy way of saying that all of your variables, procedures, functions, and so on are extracted from the code and made visible in a database table for you (or some tool) to query. I believe that seeing an example is the easiest way to understand something, so a quick demonstration is called for. First, I need to enable this feature, and I do that via a session-settable PLSCOPE_SETTINGS parameter:
create or replace procedure emp_update_sal
as
l_start_time number := 0;
l_end_time number := 0;
begin
l_start_time := dbms_utility.get_time;
for r in (select empno,ename,sal,deptno from emp)
loop
if r.deptno = 10 then
update emp
set sal = sal + (r.sal * 0.1)
where empno = r.empno;
elsif r.deptno = 20 then
update emp
set sal = sal + (r.sal * 0.2)
where empno = r.empno;
elsif r.deptno = 30 then
update emp
set sal = sal + (r.sal * 0.3)
where empno = r.empno;
end if;
end loop;
l_end_time := dbms_utility.get_time;
dbms_output.put_line ( ' Total Time taken by Looping construct is ' || (l_end_time - l_start_time) );
l_start_time := dbms_utility.get_time;
update emp
set sal = sal + (sal * (deptno/100));
l_end_time := dbms_utility.get_time;
dbms_output.put_line ( ' Total Time taken by SQL construct is ' || (l_end_time - l_start_time) );
end;
scott@ORCL> select * from v$version;
BANNER
------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
scott@ORCL> show parameter plscope;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
plscope_settings string IDENTIFIERS:NONE
scott@ORCL> alter session set plscope_settings='identifiers:all';
Session altered.
scott@ORCL> alter procedure EMP_UPDATE_SAL compile;
Procedure altered.
scott@ORCL> SELECT name,
2 type,
3 USAGE,
4 line,
5 COL
6 FROM user_identifiers
7 WHERE object_type = 'PROCEDURE'
8 AND object_name = 'EMP_UPDATE_SAL'
9 ORDER BY name,type,line;
NAME TYPE USAGE LINE COL
------------------------------ ------------------ ----------- ---------- ----------
DBMS_OUTPUT SYNONYM REFERENCE 26 2
DBMS_OUTPUT SYNONYM REFERENCE 33 2
DBMS_UTILITY SYNONYM REFERENCE 7 18
DBMS_UTILITY SYNONYM REFERENCE 24 16
DBMS_UTILITY SYNONYM REFERENCE 28 18
DBMS_UTILITY SYNONYM REFERENCE 31 16
EMP_UPDATE_SAL PROCEDURE DEFINITION 1 11
EMP_UPDATE_SAL PROCEDURE DECLARATION 1 11
L_END_TIME VARIABLE DECLARATION 4 2
L_END_TIME VARIABLE ASSIGNMENT 4 2
L_END_TIME VARIABLE ASSIGNMENT 24 2
L_END_TIME VARIABLE REFERENCE 26 75
L_END_TIME VARIABLE ASSIGNMENT 31 2
L_END_TIME VARIABLE REFERENCE 33 71
L_START_TIME VARIABLE DECLARATION 3 2
L_START_TIME VARIABLE ASSIGNMENT 3 2
L_START_TIME VARIABLE ASSIGNMENT 7 2
L_START_TIME VARIABLE REFERENCE 26 88
L_START_TIME VARIABLE ASSIGNMENT 28 2
L_START_TIME VARIABLE REFERENCE 33 84
R ITERATOR DECLARATION 8 6
R ITERATOR REFERENCE 10 6
R ITERATOR REFERENCE 12 21
R ITERATOR REFERENCE 13 18
R ITERATOR REFERENCE 14 9
R ITERATOR REFERENCE 16 21
R ITERATOR REFERENCE 17 18
R ITERATOR REFERENCE 18 9
R ITERATOR REFERENCE 20 21
R ITERATOR REFERENCE 21 19
30 rows selected.
The listing shows me all referenced identifiers (the package DBMS_OUTPUT that I invoke many times, for example) as well as all local, global, and parameter variables in my code. It shows me not only the variables but also how they are used, where they are defined, where I reference them, and where I assign to them.
scott@ORCL> SELECT iden.line,
2 iden.USAGE,
3 src.text
4 FROM user_source src,
5 user_identifiers iden
6 WHERE src.name = 'EMP_UPDATE_SAL'
7 AND src.type = 'PROCEDURE'
8 AND src.name = iden.object_name
9 AND src.type = iden.object_type
10 AND src.line = iden.line
11 AND iden.name = 'L_END_TIME'
12 ORDER BY iden.line;
LINE USAGE TEXT
---- --------------- ---------------------------------------------------
4 DECLARATION l_end_time number := 0;
4 ASSIGNMENT l_end_time number := 0;
24 ASSIGNMENT l_end_time := dbms_utility.get_time;
26 REFERENCE dbms_output.put_line ( ' Total Time taken by Looping construct is ' || (l_end_time - l_start_time) );
31 ASSIGNMENT l_end_time := dbms_utility.get_time;
33 REFERENCE dbms_output.put_line ( ' Total Time taken by SQL construct is ' || (l_end_time - l_start_time) );
6 rows selected.
Note how I can focus right in on where and how the L_END_TIME Variable is used.
Cool, thanks.
ReplyDelete