Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







PL/SQL best practice Standards tips

Oracle Tips by Jonathan Ingram

SQL and PL/SQL Coding Standards

PL/SQL has become a mature and popular language since its introduction in 1991, yet the language is still relatively young compared to its ancestors (both direct ancestors, like Ada, and indirect ancestors, like COBOL and Fortran). One of the most common questions I am asked is about coding standards for SQL and PL/SQL.

The primary purpose of coding standards has always been to make maintenance easier for developers. In order to satisfy this requirement, PL/SQL coding standards must address several areas of the development process. The Table below displays some of the development areas that PL/SQL coding standards address.

Development areas addressed by PL/SQL coding standards.


Development Area


Vertical spacing

Spacing between statements; spacing between procedures and functions within package bodies.

Horizontal spacing

Spacing between identifiers and operators; number of statements per line; maximum line width.

Procedural calls

Use of positional or named notation when calling stored PL/SQL objects.


Type of comments to be used and the frequency, spacing, positioning, and content of comments.

Code reuse/modularity

Contents of procedures and functions; organization of procedures and functions into packages.


Rules for naming identifiers; rules for naming stored PL/SQL objects.

SQL statements

Formatting rules for embedded SQL statements.


Performance tips (particularly for embedded SQL statements).


A standard method of handling exceptions inside PL/SQL objects as well as inside other applications that call stored PL/SQL objects.


Rules for writing stored PL/SQL objects so that unit testing can be accomplished.

Development environment

Rules determining what tools will be used for application development and what processes must be followed by developers.


Rules determining which keywords will be used in UPPER case, Mixed case, and lower case.

Conformance to standards

Rules designating when it is allowable for code to not agree with the coding standard.


Rules that designate whether the coding standards will be applied to design documents and what type of documentation must exist for particular routines.

The level of specifics contained in a coding standard should be fairly strict. While this may seem to be a burden during the development process, the people who do maintenance down the line will appreciate adherence to the standards.

The best way to ensure adherence to standards is to use structured peer review when a code module is completed. If the code is readable, the peer review process will flow more smoothly, and the reviewers will be able to concentrate their review time on understanding the intimacies of code rather than on deciphering an entry for the obfuscated PL/SQL contest! Peer reviews also provide a last line of defense against 'sleeper? bugs (often overlooked by even the best developers), which are found a lot more easily when the code is readable.

If your organization does some or all of its own SQL and PL/SQL training, you should make an effort to incorporate standards training into your course materials.

Introduction to PL/SQL standards

This document defines the SQL and PL/SQL environment and programming standards and procedures for <insert your company name here>. The standards established in this document apply to all SQL and PL/SQL development efforts.

These PL/SQL coding standards were written to allow for consistency in PL/SQL code written by various developers while allowing for some individual styles and preferences to be expressed. The central purpose of any coding standard for SQL and PL/SQL must deal with database performance, clarity of code, and maintainability of code. This standard should be considered a guideline for developing easily maintainable SQL and PL/SQL applications in a high-performance Oracle database.

Developers should attempt to meet the spirit of this document by applying good judgment, rather than strictly adhering to the letter of the standard. This standard applies to all developer-written SQL and PL/SQL code (including scripts, stored procedures and functions, database triggers, and stored packages). Generated code is not governed by this standard.

This document is a living document that evolves based on the experiences of you, the developer. You should be aware that changes may occur to this document in the future, based on your (or other developers?) experiences and insights

PL/SQL Naming Conventions

Using a set of naming conventions for PL/SQL objects tends to create more meaningful identifiers. This section of the standard will come into play most often when creating identifiers (variables and constants) inside blocks of code.

It is common to utilize abbreviations to shorten identifiers. When doing so, the abbreviations should be meaningful and used consistently (e.g., do not use both ADDR and ADRS as an abbreviation for ADDRESS).

More PL/SQL and SQL standards

These guidelines are provided to give code a generally consistent appearance, including indentation, horizontal alignment, and vertical alignment. Adherence to these standards will make code more readable and more easily understood when maintenance is necessary.


This is an excerpt from "High Performance Oracle Database Automation", by Jonathan Ingram and Donald K. Burleson, Series Editor.

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.