PLSQL Naming Convention Checking
Overview
Though we can set down some rules for the PL/SQL naming convention, it’s totally depending on developers’ morality to obey or ignore these rules. Fortunately, Oracle 11g introduces a new feature called “PL/SCOPE” which can be used for naming violation examination. By querying the Oracle view “USER_IDENTIFIERS”, you can know which variable in PL/SQL code violates the rules we have made. However, it’s not trivial to do this by simply querying this view, it’s good to have some utility wrapper for this. This article is intended to introduce the package “PACK_PLSQL_DEV_TOOLKIT” (currently, there is only one function in the package) you can use to check your PL/SQL code for naming convention violation.
Naming Convention Rules
|
Category
|
Naming Convention
|
Naming Case
|
|
Global variable
|
g_<name>
|
Lower case
|
|
Local variable
|
l_<name>
|
Lower case
|
|
Local constant
|
C_<NAME>
|
Upper case
|
|
Global constant
|
GC_<NAME>
|
Upper case
|
|
Exception
|
e_<name>
|
N/A
|
|
Type definition
|
T_<name>
|
N/A
|
|
procedure/function parameter
|
p_<name>
|
N/A
|
And there is another rule: No global variable in package specification.
User Guide
1. Prerequisites
- Please note that “PL/SCOPE” is available in Oracle 11g (and later) only, so you cannot have the package “PACK_PLSQL_DEV_TOOLKIT” compiled in your Oracle database if your db is prior to 11g.
- Before using the package, you need to compile your package that needs to be checked with the setting: PLSCOPE_SETTINGS=IDENTIFIERS:ALL.
For example,
ALTER PACKAGE PACK_PLSQL_DEV_TOOLKIT COMPILE PLSCOPE_SETTINGS='identifiers:all';
Alternatively, you can issue the following command first and then compile your package…
alter session set PLSCOPE_SETTINGS='identifiers:all';
or
or
alter system set PLSCOPE_SETTINGS='identifiers:all';
I recommend you set the setting system-widely, i.e. using “alter system…”
2. Create the package PACK_PLSQL_DEV_TOOKLIT in your database.
CREATE OR REPLACE TYPE t_plsql_code_analysis_obj
IS OBJECT
(
source VARCHAR2(5000),
name VARCHAR2(30),
message VARCHAR2(1000)
);
/
CREATE OR REPLACE TYPE t_plsql_code_analysis IS TABLE OF t_plsql_code_analysis_obj;
/
CREATE OR REPLACE PACKAGE PACK_PLSQL_DEV_TOOLKIT
AS
/*------------------------------Attention--------------------*/
--This package used Oracle 11g PLSCOPE feture for code analysis,
--so this package should be used only in 11g
--
--Before use this package, remember compile the package(procedure,etc)
--with PLSCOPE_SETTINGS=IDENTIFIERS:ALL
--e.g.
--ALTER PACKAGE PACK_PLSQL_DEV_TOOLKIT COMPILE PLSCOPE_SETTINGS='identifiers:all';
--Or
--alter session set PLSCOPE_SETTINGS='identifiers:all' and then compile packages
/*------------------------------------------------------------*/
/*
* Check whether the code violate the following rules...
*
*--type definitions should be named starting with t_
*--global (package level) variables should be called g_<name>
*--parameters are named p_<parameter description>
*--exceptions are named e_<name>
*--local constant are named c_<name>
*--global constant are named gc_<name>
*--local variables have names starting with l_<name>
*--variable and parameter names should be written in lowercase
*--plus: no global variables in package spec
*
*@param p_object_name the plsql code unit name, e.g. package, procedure, function
*@usage SELECT * FROM TABLE(pack_plsql_dev_toolkit.check_naming_violation('PACK_PLSQL_DEV_TOOLKIT'));
*/
FUNCTION check_naming_violation(p_object_name IN VARCHAR2) RETURN t_plsql_code_analysis PIPELINED;
END PACK_PLSQL_DEV_TOOLKIT;
/
CREATE OR REPLACE PACKAGE BODY PACK_PLSQL_DEV_TOOLKIT
AS
/*------------------------------Attention--------------------*/
--This package used Oracle 11g PLSCOPE feture for code analysis,
--so this package should be used only in 11g
--
--Before use this package, remember compile the package(procedure,etc)
--with PLSCOPE_SETTINGS=IDENTIFIERS:ALL
--e.g.
--ALTER PACKAGE PACK_PLSQL_DEV_TOOLKIT COMPILE PLSCOPE_SETTINGS='identifiers:all';
--Or
--alter session set PLSCOPE_SETTINGS='identifiers:all' and then compile packages
/*------------------------------------------------------------*/
/*
* Check whether the code violate the following rules...
*
*--type definitions should be named starting with t_
*--global (package level) variables should be called g_<name>
*--parameters are named p_<parameter description>
*--exceptions are named e_<name>
*--local constant are named c_<name>
*--global constant are named gc_<name>
*--local variables have names starting with l_<name>
*--variable and parameter names should be written in lowercase
*--plus: no global variables in package spec
*
*@param p_object_name the plsql code unit name, e.g. package, procedure, function
*@usage SELECT * FROM TABLE(pack_plsql_dev_toolkit.check_naming_violation('PACK_PLSQL_DEV_TOOLKIT'));
*/
FUNCTION check_naming_violation(p_object_name IN VARCHAR2)
RETURN t_plsql_code_analysis PIPELINED
AS
BEGIN
FOR rec IN
(
WITH identifiers AS
(
SELECT ui.name,
ui.type,
ui.usage,
ui.line,
ui.object_type,
ui.object_name,
us.text AS source
FROM user_identifiers ui,
user_source us
WHERE ui.object_name = us.name
AND ui.object_type = us.type
AND ui.line = us.line
AND ui.usage = 'DECLARATION'
AND ui.object_name = UPPER(p_object_name)
--AND ui.object_type = 'PACKAGE BODY'
),
global_section AS
(
SELECT min(line) AS end_line
FROM identifiers
WHERE type IN ('PROCEDURE', 'FUNCTION')
AND object_type = 'PACKAGE BODY'
),
names_violation AS
(
SELECT object_name || '[' || object_type || '] @ line<' || line || '>: ' || source AS source,
name AS var_name,
-- 1. parameters are named p_<parameter description>
CASE WHEN type IN ('FORMAL IN','FORMAL OUT','FORMAL IN OUT')
--AND usage = 'DECLARATION'
THEN CASE WHEN SUBSTR(name, 1, 2) <> 'P_'
THEN 'procedure(function) parameter violation: should be called p_<name>!'
END
-- 2. type definitions should be named starting with t_
WHEN type IN ('ASSOCIATIVE ARRAY', 'SUBTYPE', 'RECORD', 'NESTED TABLE')
--AND usage = 'DECLARATION'
THEN CASE WHEN SUBSTR(name, 1, 2) <> 'T_'
THEN 'type name violation: should be called t_<name>!'
END
-- 3. Local constant name should be named starting with c_ and in upper case
-- Global constant name should be named starting with gc_ and in upper case
WHEN type in ('CONSTANT')
--AND usage = 'DECLARATION'
THEN CASE WHEN line < global_section.end_line
AND (SUBSTR(name, 1, 3) <> 'GC_' OR INSTR(source, name) = 0)
THEN 'global constant name violation: should be called GC_<NAME> and in upper case!'
WHEN line > global_section.end_line
AND (SUBSTR(name, 1, 2) <> 'C_' OR INSTR(source, name) = 0)
THEN 'local constant name violation: should be called C_<NAME> and in upper case!'
END
-- 4. Exception name should be named starting with e_
WHEN type in ('EXCEPTION')
--AND usage = 'DECLARATION'
THEN CASE WHEN SUBSTR(name, 1, 2) <> 'E_'
THEN 'exception name violation: should be called e_<name>!'
END
-- 5. local variables name should be starting with l_<game>
-- gocal variables name should be starting with g_<game>
WHEN type in ('VARIABLE')
--AND usage = 'DECLARATION'
THEN CASE WHEN line < global_section.end_line
AND (SUBSTR(name, 1, 2) <> 'G_' OR INSTR(source, LOWER(name)) = 0)
THEN 'global variable name violation: should be called g_<name> and in lower case!'
WHEN line > global_section.end_line
AND (SUBSTR(name, 1, 2) <> 'L_' OR INSTR(source, LOWER(name)) = 0)
THEN 'local variable name violation: should be called l_<name> and in lower case!'
END
END AS violation
FROM identifiers,
global_section
WHERE --usage = 'DECLARATION'
TYPE IN ('FORMAL IN','FORMAL OUT','FORMAL IN OUT',
'ASSOCIATIVE ARRAY', 'SUBTYPE', 'RECORD', 'NESTED TABLE',
'CONSTANT',
'EXCEPTION',
'VARIABLE')
),
global_violation AS
(
SELECT object_name || '[' || object_type || '] @ line<' || line || '>: ' || source AS source,
name AS var_name,
'global variable should not defined in package specification!' AS violation
FROM identifiers
WHERE object_type = 'PACKAGE'
AND type IN ('VARIABLE')
AND usage = 'DECLARATION'
)
SELECT source,
var_name,
violation
FROM names_violation
WHERE violation IS NOT NULL
UNION ALL
SELECT source,
var_name,
violation
FROM global_violation
)
LOOP
PIPE ROW(t_plsql_code_analysis_obj(rec.source, rec.var_name, rec.violation));
END LOOP;
RETURN;
END check_naming_violation;
END PACK_PLSQL_DEV_TOOLKIT;
/
3. To use the package is quite easy, you can just issue a SQL statement to examine your package, for example…
select * from table(PACK_PLSQL_DEV_TOOLKIT.check_naming_violation('PACK_LO_RUN_GEM_PROCESS'));
If you get some rules (mentioned in the section “Naming Convention Rules”) violated, you will get some records presented to you, like….
If you get no records returned, then congratulations that you have no rule violated!
--------------------------------------
Regards,
FangwenYu
