PL/SQL Standards

Oracle PL/SQL Naming Conventions and Coding Standards

As an author and trainer on the PL/SQL language, I (Steven Feuerstein) am often asked about my naming conventions and coding standards. My answer has generally been a combination of muttering and vague statements and hand-waving. That's because I hadn't taken the time to write down the standards I do follow. No more!

Below you will find a link to a document that offers my ideas regarding coding standards. This is a work in progress. If you have disagreements with my approach, have a suggestion for something to add to this document, or would like to offer your own set of naming conventions and coding standards to the world of PL/SQL, send me a note at steven.feuerstein@quest.com. If I agree with it, I will put it into the document (and give you credit!) or make it available on this web page.

Steven's Naming Conventions and Coding Standards

PDF Document, 103KB
Last Updated: 5/28/2009

Naming Conventions and Coding Standards Offered by Others

PL/SQL Standards Developed for the PL/SQL Starter Framework

These standards are offered up by Bill Coulam, a fellow PL/SQL enthusiast and author of the open-source PL/SQL Starter Framework. Bill can be reached at bill.coulam@dbartisans.com.

PDF Document, 696KB
Last Updated: 4/26/2010

Trivadis PL/SQL & SQL Coding Guidelines

This comprehensive and very nicely organized guide comes courtesy of Roger Troller, Senior Consultant of Trivadis (www.trivadis.com), a consulting and training firm based in Switzerland. Many thanks, Roger!

PDF Document, 605KB
Last Updated: 11/3/2009

Comments (Read Below)







Message	*
Enter code!

Date: 6/10/2012

Each of the constants and variables is named according to the problem domain concepts that are being represented. Metadata that is not intrinsic to the problem domain is excluded from the name. A practice that I highly recommend, but did not mention in my previous post, is to include units as part of names whenever measurements are represented without a companion constant or variable to indicate units.

The absence of type, scope, and mutability metadata in variables' and constants' names should not hinder one's ability to understand the code; in fact the code may more readable because noise has been removed. As demonstrated by this brief example, eliminating such metadata can also reduce the effort required to restructure code in response to new requirements.

I highly recommend that anyone interested in writing quality code read "Clean Code: A Handbook of Agile Software Craftsmanship" by Robert C. Martin. Although the book is Java centric, the principles are applicable to software development with many programming languages. In regard to encodings in variables' names, Martin has the following to say:

"We have enough encodings to de

Date: 6/10/2012

As with most software projects, the requirements change and new capabilities have to be added: in this case a function to compute Fahrenheit to Celsius.

CREATE OR REPLACE
PACKAGE unit_conversions
IS
FUNCTION celsius_to_fahrenheit(degrees_celsius IN NUMBER)
RETURN NUMBER;
FUNCTION fahrenheit_to_celsius(degrees_fahrenheit IN NUMBER)
RETURN NUMBER;
END unit_conversions;
/
CREATE OR REPLACE PACKAGE body unit_conversions IS
-- Now that the constants are needed by more than one function, they have
-- been moved to package level to avoid duplication. Because the names did
-- not include scope metadata, they have not been changed.
absolute_zero_in_celsius CONSTANT NUMBER := - 273.15;
absolute_zero_in_fahrenheit CONSTANT NUMBER := - 459.67;
kelvin_to_rankine_factor CONSTANT NUMBER := 1.8;
rankine_to_kelvin_factor CONSTANT NUMBER := 1 / kelvin_to_rankine_factor;
FUNCTION celsius_to_fahrenheit(degrees_celsius IN NUMBER)
RETURN NUMBER
IS
-- Except for moving the constants from here to package level, this function
-- has not required any changes.
BEGIN
RETURN(degrees_celsius - absolute_zero_in_celsius) * kelvin_to_rankine_factor + absolute_zero_in_fahrenheit;
END celsius_to_fahrenheit;
FUNCTION fahrenheit_to_celsius(degrees_fahrenheit IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN(degrees_fahrenheit - absolute_zero_in_fahrenheit) * rankine_to_kelvin_factor + absolute_zero_in_celsius;
END fahrenheit_to_celsius;
END unit_conversions;
/

Date: 6/9/2012

Steven, as you requested I am posting examples that demonstrate the variable naming conventions I advocate. I’ll begin by creating a package to provide unit conversion functions. Initially it includes a single function to convert degrees Celsius to degrees Fahrenheit because that is the only capability required. (The conversion algorithms are functionally correct; however, they were coded as examples of my preferred variable name conventions and not as recommended function implementations.)

CREATE OR REPLACE PACKAGE unit_conversions IS
FUNCTION celsius_to_fahrenheit( degrees_celsius IN NUMBER)
RETURN NUMBER;
END unit_conversions;
/
CREATE OR REPLACE PACKAGE body unit_conversions IS
FUNCTION celsius_to_fahrenheit(degrees_celsius IN NUMBER)
RETURN NUMBER
IS
-- These constants are only required for this function, so they have been
-- declared within the function in order to keep their declarations near
-- their use.
absolute_zero_in_celsius CONSTANT NUMBER := - 273.15;
absolute_zero_in_fahrenheit CONSTANT NUMBER := - 459.67;
kelvin_to_rankine_factor CONSTANT NUMBER := 1.8;
BEGIN
RETURN(degrees_celsius - absolute_zero_in_celsius) * kelvin_to_rankine_factor + absolute_zero_in_fahrenheit;
END celsius_to_fahrenheit;
END unit_conversions;
/

Date: 6/1/2012

John (who wrote the May 31st post),

Would it be possible for you to post some code that follows what you advocate today? Your ideas make perfect sense, but it would be great to see what the result looks like.

Thanks, SF

Date: 5/31/2012

Steven:

I recently read your "PL/SQL Naming Conventions and Coding Standards." The following comments were prompted by the question regarding distinguishing the various levels of scope.

Having over 30 years of software development experience, I've been exposed to, and used, many naming conventions with many programming languages. It is not uncommon for one to continue the practices learned from previous programming environments. Unfortunately, it is often the case that these practices are not evaluated for their applicability to new environments. Some conventions, such as encoding type into variables' names, may be applicable when programming in Assembler; however, languages such as PL/SQL have more suitable mechanisms available.

Based on years of experience, I now advocate the following:

Variables' names should describe only the problem domain concept being represented.

From the compiler's perspective, variables' names are identifiers used to reference data and as such any name that conforms to the requirements of the language are equally valid. From the human perspective, however, variables' names are comments as well as identifiers. A general guideline for comments is that they should provide important information that is not available from the source code. Naming a variable based on the problem domain concept represented by its content is consistent with this guideline; including type, scope, or mutability is not. Type and mutability are easily ascertained from a variable's declaration. A program's structure determines scope. Changing type, scope, or mutability does not alter the problem domain concept represented by a variable.

A widely accepted coding best practice is succinctly referred to as "Don't repeat yourself" (DRY). An important reason for adhering to the DRY principal is that it minimizes the number of places code must be modified to remain consistent when changes are made. Much of the literature only discusses DRY as it pertains to program function; however, it is equally applicable to other aspects of source code.

Encoding metadata (such as type, scope, or mutability) into a variable's name violates the DRY principle because it repeats information that is already available elsewhere in the code. If any of these attributes change, then the variable's name must be changed to remain consistent with implementation. One may elect not to alter the variable's name since the program's function would not be affected; however, that would transform the name's metadata commentary into a lie.

You reject the inclusion of type indicators in variables' names because doing so "uses up … precious real estate" and because the type might change, which would then require renaming the variable; however, you advocate encoding scope and mutability. The reasoning seems inconsistent. Any encoding of metadata in the name reduces the number of characters remaining to describe the variable's meaning. Type might be more likely to change than either scope or mutability, but any of these could change.

Your original question demonstrates a recognition that the method suggested in your naming conventions of encoding scope (local scope in particular) into variables' names is inadequate to describe all of the possible levels of scope that might occur. The PL/SQL language has a means of indicating scope that is unambiguous; there is no reason to invent naming conventions to perform the same function. If one wishes to explicitly indicate scope when a variable is referenced, then this is best done by qualifying the name with the name of the block in which the variable is declared.