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

7 Mar

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):=’HAFEEZ UR RAHMAN’;
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.

source : internet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: