Generating PL/SQL Compiler Warnings (Java style) using PL/Scope

Oracle 11g - Generating PL/SQL Compiler Warnings (Java style) using PL/Scope

Purpose: 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:

SQL> create or replace procedure test_program is
v_name varchar2(100):='Hey Baby';
v_salary number(10);
v_job varchar2(20);
v_hiredate date;
v_mgr number(4);

begin
v_salary:= 40172;
dbms_output.put_line('My Name is '||v_name);
dbms_output.put_line('My Job is '||v_job);
dbms_output.put_line('I started this job on '||v_hiredate);
v_hiredate:= sysdate-1;
dbms_output.put_line('Sorry, in fact I was hired yesterday.');
v_name:='Lucas Jellema';
dbms_output.put_line('My full name is…');
v_salary:= v_salary + 500;

end test_program;
Using the information PL/Scope gathered, we can tell the following:
COMPILER_WARNING
V_MGR: variable is declared but never used (line 7)

V_JOB: reference on line 11 but variable may not be initialized (assigned a value)

V_HIREDATE: reference to variable on line 12 comes before the earliest assignment. Variable may not have been initialized on line 12

V_HIREDATE: assignment on line 13 is never used. Last reference to the variable is on line 12

V_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. You will see in this article how you can use the PL/Scope data to retrieve such warnings about PL/SQL programs.

We get USER_IDENTIFIERS with the following compiler setting:
SQL> alter session set PLSCOPE_SETTINGS='identifiers:all' ;
Now if we recompile the stored procedure as:

SQL> alter procedure test_program recompile ;

We can query USER_IDENTIFIERS to find out about the identifiers used in our program:

SQL> select name, type, usage, line
from user_identifiers
where object_name = 'TEST_PROGRAM'
and object_type = 'PROCEDURE'
order by name, type, line ;

Check the result it will show some synonum entries of dbms_output, our procedure parameters, and our procedure test_program etc. please check the result yourself.
We can join USER_IDENTIFIERS with USER_SOURCE to get some more insight in exactly how the identifier is used in.


SQL> 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 = 'TEST_PROGRAM'
and s.type = 'PROCEDURE'
order by name, type, line ;


Please check the out put of this query yourself, and compare the two results.

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:
1. variables that are referenced but never assigned (before that reference)
2. variables that are declared but never used
3. 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:
SQL> alter procedure test_program recompile ;

We can query USER_IDENTIFIERS to find out about the identifiers used in our program:

SQL> select name, type, usage, line
from user_identifiers
where object_name = 'TEST_PROGRAM'
and object_type = 'PROCEDURE'
order by name, type, line ;

Check the result it will show some synonum entries of dbms_output, our procedure parameters, and our procedure test_program etc. please check the result yourself.
We can join USER_IDENTIFIERS with USER_SOURCE to get some more insight in exactly how the identifier is used in.

Download the following text file and run the program on your Oracle 11g SQL prompt, and then check the result.


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 = 'TEST_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.
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 ;