Yesterday I wrote a blog article on how to use the Oracle 11g PL/Scope compiler setting to derive compiler warnings about potential incorrect usage of variables. This incorrect usage consists of variables being declared but never used, variables being referenced before they have been assigned a value and variables being assigned a value that is never referred to (see https://technology.amis.nl/blog/?p=2581 ).
Last night I discussed this article with Michael Rosenblum from Dulcian Software and he told me that he was using the USER_IDENTIFIERS generated by the PL/SQL compiler with the correct PL/Scope settings to enforce naming conventions. Before we got into any details, we moved onto other topics, but his suggestion settled itself in the back of my mind. That was a very interesting idea! Before, to enforce any kind of naming convention, we had to try to parse the PL/SQL code and make some educated guesses about variable definitions and their relative position in the program unit. Using USER_IDENTIFIERS that becomes very much easier.
Let’s take a look at what it could mean to use USER_IDENTIFIERS for enforcing naming conventions for Variables and other identifiers such as Parameters, Types and Program Units.
Our naming conventions among others include these guidelines:
– type definitions should be named starting with t_
– global (package level) variables should be called g_…..
– parameters are named p_<parameter description>
– local variables have names starting with l_
– variable and parameter names should be written in lowercase
Another rule is that there may not be global variables in package specifications.
To be able to enforce – well actually we are only verifying whether or not these rules are adhered to – we need to gather identifier details from the PL/SQL compiler. We do so by setting the right compiler setting and then (re)compiling the program units we will inspect.
alter session set PLSCOPE_SETTINGS='identifiers:all' /
The PL/SQL unit under scrutiny today is such a great package that I even called it GREAT_PACKAGE. However, it will be our job to find out whether in fact it is such a great package.
create or replace
package great_package
is
some_public_global_variable number(10);
g_and_another_one boolean;
procedure wonderful_program
( input_param1 in number
, p_param2 in out date
);
end great_package;
/
create or replace
package body great_package
is
type emprec is record (l_name varchar2(20), job varchar2(20));
g_emp emprec;
global_1 positive;
procedure wonderful_program
( input_param1 in number
, p_param2 in out date
) is
name varchar2(100):='LUCAS';
v_salary number(10,2);
b_job varchar2(20);
hireDate date;
begin
v_salary:= 5432.12;
dbms_output.put_line('My Name is '||name);
dbms_output.put_line('My Job is '||b_job);
hireDate:= sysdate-1;
dbms_output.put_line('I started this job on '||hireDate);
end wonderful_program;
end great_package;
/
Compiling this package will have led to the creation of a bunch of User Identifiers:
select name , type , usage , line from user_identifiers where object_name = 'GREAT_PACKAGE' order by object_type , name , type , line OBJECT_TYPE NAME TYPE USAGE LINE ------------- ------------------------------ ------------------ ----------- -------- PACKAGE GREAT_PACKAGE PACKAGE DECLARATION 1 PACKAGE G_AND_ANOTHER_ONE VARIABLE DECLARATION 4 PACKAGE INPUT_PARAM1 FORMAL IN DECLARATION 6 PACKAGE P_PARAM2 FORMAL IN OUT DECLARATION 7 PACKAGE SOME_PUBLIC_GLOBAL_VARIABLE VARIABLE DECLARATION 3 PACKAGE WONDERFUL_PROGRAM PROCEDURE DECLARATION 5 PACKAGE BODY B_JOB VARIABLE DECLARATION 12 PACKAGE BODY B_JOB VARIABLE REFERENCE 17 PACKAGE BODY DBMS_OUTPUT SYNONYM REFERENCE 16 PACKAGE BODY DBMS_OUTPUT SYNONYM REFERENCE 17 PACKAGE BODY DBMS_OUTPUT SYNONYM REFERENCE 19 PACKAGE BODY EMPREC RECORD DECLARATION 3 PACKAGE BODY EMPREC RECORD REFERENCE 4 PACKAGE BODY GLOBAL_1 VARIABLE DECLARATION 5 PACKAGE BODY GREAT_PACKAGE PACKAGE DEFINITION 1 PACKAGE BODY G_EMP VARIABLE DECLARATION 4 PACKAGE BODY HIREDATE VARIABLE DECLARATION 13 PACKAGE BODY HIREDATE VARIABLE ASSIGNMENT 18 PACKAGE BODY HIREDATE VARIABLE REFERENCE 19 PACKAGE BODY INPUT_PARAM1 FORMAL IN DECLARATION 7 PACKAGE BODY JOB VARIABLE DECLARATION 3 PACKAGE BODY L_NAME VARIABLE DECLARATION 3 PACKAGE BODY NAME VARIABLE DECLARATION 10 PACKAGE BODY NAME VARIABLE ASSIGNMENT 10 PACKAGE BODY NAME VARIABLE REFERENCE 16 PACKAGE BODY P_PARAM2 FORMAL IN OUT DECLARATION 8 PACKAGE BODY V_SALARY VARIABLE DECLARATION 11 PACKAGE BODY V_SALARY VARIABLE ASSIGNMENT 15 PACKAGE BODY WONDERFUL_PROGRAM PROCEDURE DEFINITION 6
Usage includes: reference, declaration, assignment, definition (for objects inside packages, similar to what declaration is for variables)
Type includes: synonym, variable, iterator, formal in, formal in out, record, procedure, package.
— create query to search for possible naming convention violations
- type definitions should be named starting with t_
- global (package level) variables should be called g_…..
- parameters are named p_<parameter description>
- local variables have names starting with l_
- variable and parameter names should be written in lowercase
- plus: no global variables in package spec
with identifiers as
( select i.name
, i.type
, i.usage
, s.line
, i.object_type
, i.object_name
, s.text source
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 object_name = 'GREAT_PACKAGE'
)
, global_section as
( select min(line) end_line
, object_name
from identifiers
where object_type = 'PACKAGE BODY'
and type in ('PROCEDURE','FUNCTION')
group
by object_name
)
, naming_convention_violations
as
( select name identifier
, 'line '||line||': '||source sourceline
, case
when type = 'RECORD'
and usage = 'DECLARATION'
and substr(lower(name),1,2) <> 't_'
then 'Violated convention that type definitions should be called t_<name>'
when type in ('FORMAL IN', 'FORMAL IN OUT', 'FORMAL OUT')
and usage = 'DECLARATION'
and substr(lower(name),1,2) <> 'p_'
then 'Violated convention that (input and output) parameters should be called p_<name>'
when type = 'VARIABLE'
and usage = 'DECLARATION'
then case
when line < global_section.end_line -- global variable
and substr(lower(name),1,2) <> 'g_'
then 'Violated convention that global variables should be called g_<name>'
when line > global_section.end_line -- local variable
and substr(lower(name),1,2) <> 'l_'
then 'Violated convention that local variables should be called l_<name>'
end
end message
from identifiers
join
global_section
using
( object_name )
)
, global_violations
as
( select name identifier
, 'line '||line||': '||source sourceline
, case
when type = 'VARIABLE'
and usage = 'DECLARATION'
and object_type = 'PACKAGE'
then 'Violated convention that there should not be any Global Variables in a Package Specification'
end message
from identifiers
)
, casing_violations
as
( select name identifier
, 'line '||line||': '||source sourceline
, case
when type = 'VARIABLE'
and usage = 'DECLARATION'
and instr(source, lower(name)) = 0
then 'Violated convention that variable names should spelled in lowercase only'
end message
from identifiers
)
, convention_violations as
( select *
from naming_convention_violations
union all
select *
from global_violations
union all
select *
from casing_violations
)
select *
from convention_violations
where message is not null
/
The result of executing this query is a list of warnings, identifying violations of the guidelines we have for programming proper PL/SQL code:
IDENTIFIER ------------------------------ SOURCELINE -------------------------------------------------------------------------------- MESSAGE -------------------------------------------------------------------------------- SOME_PUBLIC_GLOBAL_VARIABLE line 3: some_public_global_variable number(10); Violated convention that global variables should be called g_<name> INPUT_PARAM1 line 6: ( input_param1 in number Violated convention that (input and output) parameters should be called p_<name> JOB line 3: type emprec is record (l_name varchar2(20), job varchar2(20)); Violated convention that global variables should be called g_<name> L_NAME line 3: type emprec is record (l_name varchar2(20), job varchar2(20)); Violated convention that global variables should be called g_<name> EMPREC line 3: type emprec is record (l_name varchar2(20), job varchar2(20)); Violated convention that type definitions should be called t_<name> GLOBAL_1 line 5: global_1 positive; Violated convention that global variables should be called g_<name> INPUT_PARAM1 line 7: ( input_param1 in number Violated convention that (input and output) parameters should be called p_<name> NAME line 10: name varchar2(100):='LUCAS'; Violated convention that local variables should be called l_<name> V_SALARY line 11: v_salary number(10,2); Violated convention that local variables should be called l_<name> B_JOB line 12: b_job varchar2(20); Violated convention that local variables should be called l_<name> HIREDATE line 13: hireDate date; Violated convention that local variables should be called l_<name> SOME_PUBLIC_GLOBAL_VARIABLE line 3: some_public_global_variable number(10); Violated convention that there should not be any Global Variables in a Package S pecification G_AND_ANOTHER_ONE line 4: g_and_another_one boolean; Violated convention that there should not be any Global Variables in a Package S pecification HIREDATE line 13: hireDate date; Violated convention that variable names should spelled in lowercase only
So from the compiler generated User Identifier data, it is really easy to perform a quick audit against specific programming guidelines. And many more guidelines and standards than just these naming conventions.
Some additional things to do with user_identifiers that come to mind (though I am sure there are many more):
– generate PL/SQL Documentation
– refactoring – restructure packages relocation program units
– AOP like injection of variable value tracing logic (for example to write out logging with the values of all input parameters)
– …
Great idea!
I’m just waiting to have 11g in my development environment to apply similar code to test our rules! I’m sure that many naming error will be found.
the problem is that we havewill take some time migration to it due to lots of code with “departed” Oracle Workflow