Enforcing PL/SQL naming conventions through a simple SQL query (using Oracle 11g PL/Scope)

1

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 http://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) &lt;&gt; 't_'
         then 'Violated convention that type definitions should be called t_&lt;name&gt;'
         when type in ('FORMAL IN', 'FORMAL IN OUT', 'FORMAL OUT')
              and    usage = 'DECLARATION'
              and    substr(lower(name),1,2) &lt;&gt; 'p_'
         then 'Violated convention that (input and output) parameters should be called p_&lt;name&gt;'
         when type  = 'VARIABLE'
              and   usage = 'DECLARATION'
         then case
              when   line &lt; global_section.end_line -- global variable
                     and    substr(lower(name),1,2) &lt;&gt; 'g_'
              then 'Violated convention that global variables should be called g_&lt;name&gt;'
              when   line &gt; global_section.end_line -- local variable
                     and    substr(lower(name),1,2) &lt;&gt; 'l_'
              then 'Violated convention that local variables should be called l_&lt;name&gt;'
              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_&lt;name&gt;            
                                                                               
INPUT_PARAM1                                                                   
line 6: ( input_param1 in number                                               
Violated convention that (input and output) parameters should be called p_&lt;name&gt;
                                                                               
JOB                                                                            
line 3: type emprec is record  (l_name varchar2(20), job varchar2(20));        
Violated convention that global variables should be called g_&lt;name&gt;            
                                                                               
L_NAME                                                                         
line 3: type emprec is record  (l_name varchar2(20), job varchar2(20));        
Violated convention that global variables should be called g_&lt;name&gt;            
                                                                               
EMPREC                                                                         
line 3: type emprec is record  (l_name varchar2(20), job varchar2(20));        
Violated convention that type definitions should be called t_&lt;name&gt;            
                                                                               
GLOBAL_1                                                                       
line 5: global_1 positive;                                                     
Violated convention that global variables should be called g_&lt;name&gt;            
                                                                               
INPUT_PARAM1                                                                   
line 7: ( input_param1 in number                                               
Violated convention that (input and output) parameters should be called p_&lt;name&gt;
                                                                               
NAME                                                                           
line 10:   name       varchar2(100):='LUCAS';                                  
Violated convention that local variables should be called l_&lt;name&gt;             
                                                                               
V_SALARY                                                                       
line 11:   v_salary   number(10,2);                                            
Violated convention that local variables should be called l_&lt;name&gt;             
                                                                               
B_JOB                                                                          
line 12:   b_job      varchar2(20);                                            
Violated convention that local variables should be called l_&lt;name&gt;             
                                                                               
HIREDATE                                                                       
line 13:   hireDate   date;                                                    
Violated convention that local variables should be called l_&lt;name&gt;             
                                                                               
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)
– …

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

1 Comment

  1. 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