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

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:

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:

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

Credit goes to the below website(s) :

http://technology.amis.nl/2007/11/14/oracle-11g-generating-plsql-compiler-warnings-java-style-using-plscope/