The Oracle 11g PL/Scope feature allows us to have the PL/SQL compiler generate information on all identifiers used in our PL/SQL code. Using the information generated and available from the USER_IDENTIFIERS data dictionary view, we can do some interesting things. Say for example we have the following stored procedure:
create or replace procedure wonderful_program is l_name varchar2(100):='LUCAS'; l_salary number(10,2); l_job varchar2(20); l_hiredate date; l_mgr number(4); begin l_salary:= 5432.12; dbms_output.put_line('My Name is '||l_name); dbms_output.put_line('My Job is '||l_job); dbms_output.put_line('I started this job on '||l_hiredate); l_hiredate:= sysdate-1; dbms_output.put_line('Sorry, in fact I was hired yesterday.'); l_name:='Lucas Jellema'; dbms_output.put_line('My full name is...'); l_salary:= l_salary + 10; end wonderful_program;
Can you tell some things that are wrong or at least dodgy with this procedure?
Using the information PL/Scope gathered, we can tell the following:
COMPILER_WARNING -------------------------------------------------------------------------------- L_MGR: variable is declared but never used (line 7) L_JOB: reference on line 11 but variable may not be initialized (assigned a value) L_HIREDATE: reference to variable on line 12 comes before the earliest assignment. Variable may not have been initialized on line 12 L_HIREDATE: assignment on line 13 is never used. Last reference to the variable is on line 12 L_NAME: assignment on line 15 is never used. Last reference to the variable is on line 10
Note that these warnings are very similar to what Java Compilers will warn us about. I will show in this article how we can use the PL/Scope data to retrieve such warnings about our PL/SQL programs.
We get USER_IDENTIFIERS with the following compiler setting:
alter session set PLSCOPE_SETTINGS='identifiers:all'
Now if we recompile the stored procedure:
alter procedure wonderful_program recompile
We can query USER_IDENTIFIERS to find out about the identifiers used in my program:
select name , type , usage , line from user_identifiers where object_name = 'WONDERFUL_PROGRAM' and object_type = 'PROCEDURE' order by name , type , line NAME TYPE USAGE LINE ------------------------------ ------------------ ----------- ---------- DBMS_OUTPUT SYNONYM REFERENCE 10 DBMS_OUTPUT SYNONYM REFERENCE 11 DBMS_OUTPUT SYNONYM REFERENCE 12 DBMS_OUTPUT SYNONYM REFERENCE 14 DBMS_OUTPUT SYNONYM REFERENCE 16 L_HIREDATE VARIABLE DECLARATION 6 L_HIREDATE VARIABLE REFERENCE 12 L_HIREDATE VARIABLE ASSIGNMENT 13 L_JOB VARIABLE DECLARATION 5 L_JOB VARIABLE REFERENCE 11 L_MGR VARIABLE DECLARATION 7 L_NAME VARIABLE DECLARATION 3 L_NAME VARIABLE ASSIGNMENT 3 L_NAME VARIABLE REFERENCE 10 L_NAME VARIABLE ASSIGNMENT 15 L_SALARY VARIABLE DECLARATION 4 L_SALARY VARIABLE ASSIGNMENT 9 L_SALARY VARIABLE REFERENCE 17 L_SALARY VARIABLE ASSIGNMENT 17 WONDERFUL_PROGRAM PROCEDURE DEFINITION 1 WONDERFUL_PROGRAM PROCEDURE DECLARATION 1
We can join USER_IDENTIFIERS with USER_SOURCE to get some more insight in exactly how the identifier is used in the code:
select i.name , i.type , i.usage , i.line , s.text from user_identifiers i join user_source s on ( s.name = i.object_name and s.type = i.object_type and s.line = i.line ) where s.name = 'WONDERFUL_PROGRAM' and s.type = 'PROCEDURE' order by name , type , line / NAME TYPE USAGE LINE ------------------------------ ------------------ ----------- ---------- TEXT -------------------------------------------------------------------------------- DBMS_OUTPUT SYNONYM REFERENCE 10 dbms_output.put_line('My Name is '||l_name); DBMS_OUTPUT SYNONYM REFERENCE 11 dbms_output.put_line('My Job is '||l_job); DBMS_OUTPUT SYNONYM REFERENCE 12 dbms_output.put_line('I started this job on '||l_hiredate); DBMS_OUTPUT SYNONYM REFERENCE 14 dbms_output.put_line('Sorry, in fact I was hired yesterday.'); DBMS_OUTPUT SYNONYM REFERENCE 16 dbms_output.put_line('My full name is...'); L_HIREDATE VARIABLE DECLARATION 6 l_hiredate date; L_HIREDATE VARIABLE REFERENCE 12 dbms_output.put_line('I started this job on '||l_hiredate); ....
The value of usage includes: reference, declaration, assignment, definition (for objects inside packages, similar to what declaration is for variables). Type can be many things, such as: synonym, variable, iterator, formal in, procedure, package.
Having established all of the above, let’s create a query to find typical erroneous situations in our code. We will search for these situations:
- variables that are referenced but never assigned (before that reference)
- variables that are declared but never used
- variables that are assigned but never used (after that assignment)
The compiler does not allow situations like Variable is Referenced but not Declared. That also means that a variable that is not declared in an object is declared somewhere else. Referencing such variables and assigning values to them is perfectly legitimate and should not be considerd a warnable circumstance. A variable that is declared locally but never used (referenced or assigned) is warnable, as is a variable that is locally declared, assigned but never referenced.
Note: these are all potential mistakes, but there can be a perfectly good reason too, so let’s just present the findings as hints.
The report should look like:
with identifiers as ( select name , type , usage , line , first_value(line) over (partition by name, usage order by line asc) first_line , first_value(line) over (partition by name, usage order by line desc) last_line from user_identifiers where object_name = 'WONDERFUL_PROGRAM' and object_type = 'PROCEDURE' and type = 'VARIABLE' ) , last_assignments -- the last assignment of every identifier as ( select * from identifiers where usage = 'ASSIGNMENT' and line = last_line ) , last_references -- the last reference of every identifier as ( select * from identifiers where usage = 'REFERENCE' and line = last_line ) , first_references -- the first reference of every identifier as ( select * from identifiers where usage = 'REFERENCE' and line = first_line ) , first_assignments -- the first assignment of every identifier as ( select * from identifiers where usage = 'ASSIGNMENT' and line = first_line ) , declarations -- the declaration for every identifier as ( select * from identifiers where usage = 'DECLARATION' ) -- now outer join last_assignments with last_references: when no ass, then warn ref but not ass; -- when no ref the warn: ass but no ref; -- when ass.line > ref.line then warn: assignment on line is never used select case when la.line is null then name||': reference on line '||lr.line||' but variable may not be initialized (assigned a value)' when lr.line is null then name||': a value is assigned, but there is no reference to the variable' when la.line > lr.line then name||': assignment on line '||la.line||' is never used. Last reference to the variable is on line '||lr.line end compiler_warning from last_assignments la full outer join last_references lr using (name) union all -- now outer join first_assignments with first_references: -- when ass.line > ref.line then warn: reference before any assignment is done select case when fa.line > fr.line then name||': reference to variable on line '||fr.line||' comes before the earliest assignment. Variable may not have been initialized on line '||fr.line end compiler_warning from first_assignments fa full outer join first_references fr using (name) union all -- now outer join delarations with last_references: -- when no ref then warn: variable declared but never used; -- when ref but no declaration should not occur ;(for local identifiers) nor should declaration.line > ref.line select case when fr.line is null then name||': variable is declared but never used (line '||de.line||')' end compiler_warning from declarations de full outer join last_references fr using (name) order by name
The result of this query for our little program was already at the top of the article:
COMPILER_WARNING -------------------------------------------------------------------------------- L_MGR: variable is declared but never used (line 7) L_JOB: reference on line 11 but variable may not be initialized (assigned a value) L_HIREDATE: reference to variable on line 12 comes before the earliest assignment. Variable may not have been initialized on line 12 L_HIREDATE: assignment on line 13 is never used. Last reference to the variable is on line 12 L_NAME: assignment on line 15 is never used. Last reference to the variable is on line 10
This is a very useful script. Thanks for posting it. I found one issue while using the same.
If an identifier say a flag is used in a loop. the assumption that its not referenced is wrong,
the flag value may be referenced in loop condition, but the last rerence is not greater than last assignment.
I got only this issue.
It would be nice if you can include that.
The logic is very good.
Thanks
For that kind of warnings (that you get from the compiler) I was using SQL Navigator (with formatter option) that I think is also available with TOAD.
It has plenty of great warnings tips based on PL/SQL Best Pratices Book from Steven. For instance Nested LOOPs should all be labeled, END of program unit, package or type is not labeled , etc..
Very well done Lucas!
Very nice and useful
Thanks a lot
Hector Gabriel Ulloa Ligarius
http://www.ligarius.com