Validating naming conventions in Oracle

Most development teams have agreed upon some sort of naming convention as it typically increases the readability and comprehension of the source code. Defining these rules is one thing, but correctly and consistently applying them is another. Wouldn’t it be great if we were able to actually validate whether our team-specific naming rules have been applied the right way? Well, I’ve created a package that makes it possible to list all incorrectly named elements in your database schema.

How does it work?

I make a distinction between two types of naming violations:

• Object name violations: Teams often pre- or suffix database object names to indicate the project or domain the object belongs to. Another common practice in Oracle development is to assign database objects a pre- or suffix from which you can immediately determine the object type. For example: a table for employees is named TBL_EMP, a sequence to fill up the primary key column is named SEQ_EMP, etc.
• Code identifier name violations: PL/SQL code is made up of different types of identifiers: variables, constants, types, parameters, etc. Applying naming rules to these identifiers results in more readable and understandable code. Local variables are for example often prefixed with l_, while global variables start with the g_ prefix. For this part I use PL/Scope to verify the correctness of the identifier names in your PL/SQL code.

PL/Scope is a compiler-driven tool that collects data about identifiers in PL/SQL source code at program-unit compilation time and makes it available in static data dictionary views. The collected data includes information about identifier types, usages (declaration, definition, reference, call, assignment) and the location of each usage in the source code.

It’s important to keep two things in mind here. First, PL/Scope has been introduced in Oracle 11g Release 1, which means that any lower database version is not supported. Secondly, the PL/Scope data dictionary views are populated only when the PLSCOPE_SETTINGS parameter is set to IDENTIFIERS:ALL for your current session.

alter session set plscope_settings='identifiers:all';

The default parameter value is IDENTIFIERS:NONE, which means that identifier gathering is disabled by default. Not setting the appropriate parameter value leads to inaccurate results. So make sure you set the PLSCOPE_SETTINGS parameter to IDENTIFIERS:ALL before compiling your database objects.

Installation and configuration

We first need to take care of the installation. Download the installation script (install.sql) and execute it on the database schema you wish to validate naming conventions. The installation script compiles no more than three objects:

• A package API_NAMING_CONVENTION
• An object type OT_NAMING_VIOLATION
• A table type TT_NAMING_VIOLATIONS

After a successful execution of the installation script, open the API_NAMING_CONVENTION package body. The first thing you’ll notice is the configuration section, which is nothing more than a collection of global constants. Every constant will be used inside the package to verify whether valid names have been given to a specific database object type or code identifier type. For example, the first constant you’ll encounter is GCO_TABLE_NAME_PATTERN and can be assigned a regular expression pattern that includes all rules to which a table name in your schema must comply. For those not into regular expressions, don’t worry, we’re not going to write complicated or lengthy regex patterns. I’ll give some examples later on, so there’s no reason to panic ;]

Example usage

Let’s see what the API_NAMING_CONVENTION package has to offer. In this example, I want to validate whether my table names start with the prefix TBL_ and I want my sequence names to end with _SEQ. We’ll first need to configure the API_NAMING_CONVENTION package body to take into account these naming rules. Assign the following regular expression patterns to the appropriate constants:

gco_table_name_pattern constant gt_string := '(^TBL_).+';
...
gco_sequence_name_pattern constant gt_string := '.+(_SEQ$)';

You’ll probably use these two regex patterns a lot as most naming rules include nothing more than a prefix or suffix.

I currently have no tables or sequences in my schema, so let’s fix that.

create table tbl_valid(
 column1 varchar2(255)
);

create sequence seq_invalid;

We’re now going to write a query that gives us back all improperly named objects according to our naming convention.

select *
from table(api_naming_convention.all_object_name_violations);

That query returns one row.

The name of the sequence is invalid because I expect the SEQ part at the end of the name. The table I created is not included since its name conforms to our naming convention. Okay, I have to admit that this wasn’t mind blowing so far. But wait. There’s more to come. Let me demonstrate the true powers of the API_NAMING_CONVENTION package.

Validating code identifiers

Now the fun part starts. We’re going to look for violated naming rules in our PL/SQL code. Before we decide on the rules we want to enforce, let me quickly explain the usage of the GCO_LOCAL_IDENTIFIER and GCO_GLOBAL_IDENTIFIER constants in the API_NAMING_CONVENTION package body. These two constants accept a string value, which allows you to define the character(s) you use to differentiate between locally and globally scoped elements. I use the characters L and G for respectively local and global identifiers.

gco_local_identifier constant gt_string := 'L';
gco_global_identifier constant gt_string := 'G';

We are now able to reference these values in our regular expression patterns by using the placeholder :scope:. For this example, I use the following naming rules.

gco_variable_pattern constant gt_string := '(^:SCOPE:_).+';
gco_constant_pattern constant gt_string := '(^:SCOPE:CO_).+';
gco_exception_pattern constant gt_string := '(^:SCOPE:EX_).+';
gco_cursor_pattern constant gt_string := '(^:SCOPE:CU_).+';
...

This means that a regular variable should either start with l_ or g_, depending on its scope. The rule for constants is very alike: local constants should start with lco_, while global constants should start with gco_. That makes sense, right?

Okay, configuration is all set. I’ll now create a dummy package so that the schema we’re working in contains some PL/SQL code.

create or replace package body my_package is

  -- global
  g_valid number;
  gco_valid constant varchar2(255) := 'xyz';
  invalid boolean;
  lco_invalid constant date := sysdate;

  procedure do_nothing is
    -- local
    v_invalid varchar2(255);
    lco_valid constant number := 1;
    invalid_exception exception;
    cursor lcu_valid is
      select *
      from dual;
    begin
      null;
    end do_nothing;

end my_package;

The package compiled successfully and it’s obvious that it contains at least several naming violations. It’s time for our API_NAMING_CONVENTION package to identify the infringements. Here’s the query I use to specifically check for identifier name violations.

select *
from table(api_naming_convention.all_coding_violations);

The result is a bit disappointing though. The query returns zero rows while it’s clear that my PL/SQL code contains multiple improperly named identifiers. Is something wrong with the API_NAMING_CONVENTION package or did we overlook something? Remember the PL/Scope parameter? I am required to set the PLSCOPE_SETTINGS parameter to IDENTIFIERS:ALL before compiling any PL/SQL program-unit. Let’s fix that and rerun the query.

alter session set plscope_settings='identifiers:all';
alter package my_package compile;

select *
from table(api_naming_convention.all_coding_violations);

This time the query successfully identified the violated elements in my PL/SQL code.

The result set includes detailed metadata to help you understand and locate the naming violation.

• Violation type: indicates whether it’s an OBJECT NAME or CODING violation.
• Element type: the type of database object or identifier that caused the violation.
• Element name: the object or identifier that’s been given an improper name.
• Violated pattern: the violated regular expression pattern.
• Object type: the database object type in which the violation occurred.
• Object name: the name of the database object in which the violation occurred.
• Line and column: the exact location of the improperly named identifier in the PL/SQL program-unit.
• Scope: is the violated identifier locally or globally scoped?

The ALL_VIOLATIONS function

You can query all naming violations regardless of their type by running the below query.

select *
from table(api_naming_convention.all_violations);

The result is a combination of both ALL_OBJECT_NAME_VIOLATIONS and ALL_CODING_VIOLATIONS.

It is even possible to only query for certain object or identifier type naming violations. Take a look at the API_NAMING_CONVENTION package specification for all available functions. The SEQUENCE_NAME_VIOLATIONS function for example only returns improperly named sequences.

Final words

Phew, this article is getting pretty long, so I’m going to stop right here. Congratulations for making it this far ;]

This is an open source project by the way. The source code is being maintained on GitHub. Leave me a comment in case you have any questions or remarks.