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.

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) – …

1 Comment

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

Lucas Jellema

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 & PL/SQL), Service Oriented Architecture, BPM, ADF, JavaScript, Java in various shapes and forms and many other things. Author of the Oracle Press books: Oracle SOA Suite 11g Handbook and Oracle SOA Suite 12c Handbook. Frequent presenter on conferences such as JavaOne and Oracle OpenWorld. Presenter for Oracle University Celebrity specials.

Follow us on Twitter

meta

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 322 other subscribers

Email Address

About

AMIS is internationally recognized for its deep technological insight in Oracle technology. This knowledge is reflected in the presentations we deliver at international conferences such as Oracle OpenWorld, Hotsos and many user conferences around the world. Our AMIS Technology Blog, the most referred Oracle technology knowledge base outside the oracle.com domain. However you arrived here, we appreciate your interest in AMIS. Link to our Google+ Profile AMIS