BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation









PL/SQL Coding Standards

Oracle Tips

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. Below displays some of the development areas that PL/SQL coding standards address.

Development areas addressed by 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.

I hope you find the examples presented in this text easy to read and understand. I also hope that you notice the consistency of style in the way examples appear. Every piece of code on the CD-ROM and in the text conforms to the following coding standard.

A Sample PL/SQL Coding Standard

This document defines the SQL and PL/SQL environment and programming standards and procedures for you. 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.

The Development Environment And Processes

This section of the standard is highly dependent on the nature of your organization; therefore, I will only give suggestions on the types of material that should be included in this section of your coding standard. The Development Environment And Processes section of the standard should address the following issues:

  Your version control processes—A good working knowledge of PL/SQL is important to a developer, but the inability of a single developer to follow version control processes could be disastrous!

  Standard tools for your development efforts—If you have a standard configuration for your tools, it should be described in painstaking detail. If your tools support central administration of this configuration, so much the better.

  Peer review practices—Many organizations have formal peer review processes in place to validate the quality of design documents, code, and test results. If your organization uses these processes, the coding standard should (at the very least) point developers to your process documents.

  Documentation—Describe the documentation required when developers create new code and what documents need to be updated when maintenance is performed. Provide reasonable facsimiles of this documentation (or excerpts from real documents that satisfy your requirements).

  Testing standards in place—Provide examples of thorough test scripts and plans. Tests for each aspect of your system (front end, stored procedures and functions, packages, and database triggers) should be discussed in detail.

  Standard routines—You should describe whether standard routines, such as error handling, help system calls, and so forth, are available and when these routines should be used. Provide a detailed explanation of each standard call and its interfaces.

These issues can be addressed in appendices to your coding standard or in other documents, as long as your developers receive the necessary information.

References To Other Documents
I personally favor one stop shopping; pack the standard with as much information as possible and reduce the amount of time developers spend tracking down other documents.

PL/SQL Programming Design Standards

Developers should design for modularity. Black box is a term often used in conjunction with modules; each module should perform one (and only one) function using a defined interface and produce a predictable result. So long as the interface for a code module is not changed, the code module may be altered without affecting outside code.

Each module contains one or more routines (and the data structures and variables needed to support the routines). PL/SQL allows developers to implement modularity through the use of packages, which can contain procedures and functions as well as global type, variable, and constant declarations.

Stored functions that use parameters of the IN OUT and OUT types are not allowed. Stored functions should use only the RETURN statement to return a value. Developers are encouraged to identify routines that can be reused. This code can be centralized, tested, and used by other developers to improve the reliability of system code and to reduce development time for more complex modules.

The Modularity Ombudsman
If your organization consists of ten or more people, it might be a good idea to appoint a “Modularity Ombudsman” who has some experience with SQL and PL/SQL. The responsibilities of this position include:

  Identifying code segments that can be reused

  Developing and testing reusable modules

  Documenting modules and promoting their use

The modularity ombudsman should also take part in peer review for new modules to help increase code reuse throughout your project.

PL/SQL Header Standards

A header should appear at the start of any script, procedure, function, package body, or package spec. Consider this template header:

-- *****************************************************************
-- Description: Describe the purpose of the object. If necessary,
-- describe the design of the object at a very high level.
-- Input Parameters:
-- Output Parameters:
-- Error Conditions Raised:
-- Author:      <your name>
-- Revision History
-- Date            Author       Reason for Change
-- ----------------------------------------------------------------
-- 03 JAN 1997     J.Schmoe     Created.
-- *****************************************************************

The Usefulness Of Headers

There are some people who dislike headers or feel that a header is a useless burden to place on a developer. I disagree: At no other point in the code are provisions made for documenting the overall purpose, logic, and interface of a module. In my opinion, a header is the most essential documentation for any piece of stored code.

Formatting Guidelines for PL/SQL code

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.

Alignment of PL/SQL Operators

These guidelines enhance the readability of code by adding white space and clarifying complex expressions.

  Arrange series of statements containing similar operators into columns whenever it will not cause excessive white space and you have sufficient room to do so.

  vFirstName := 'Roger';
  vLastName  := 'Smith';
  vSSN       := 999999999;


  vFirstName := 'Roger';
  vLastName := 'Smith';
  vSSN := 999999999;

  Always use parentheses in expressions containing more than one identifier or literal. This clarifies code for inexperienced developers who are not familiar with operator precedence and helps eliminate the possibility that you’ve overlooked something in your equation.

  IF (nSSN < 2.5) THEN


  IF nSSN < 2.5 THEN

  Align the IN and OUT keywords in columns when defining the interface for a procedure or function.

  PROCEDURE Days_Between (dStartDate   IN     date,
                          dEndDate     IN     date,
                          nGPA         IN OUT number,
                          nDaysBetween    OUT number)
  <procedure declarations and body>


  PROCEDURE Days_Between (dStartDate IN date,
                          dEndDate IN date,
                          nGPA IN OUT number,
                          nDaysBetween OUT number)
  <procedure declarations and body>

  When calling a procedure or function, align the parameters into a column. This reduces the visual clutter around the call, making it stand out from the rest of the code.

  DaysBetween (dStartDate   => dEnrolledDate,
               dEndDate     => dGraduationDate,
               nGPA         => nFinalGPA,
               nDaysBetween => nDuration);


  DaysBetween (dStartDate => dEnrolledDate,
               dEndDate => dGraduationDate,
               nGPA => nFinalGPA,
               nDaysBetween => nDuration);

PL/SQL Capitalization

Below contains a list of keywords that should always be fully capitalized when referenced in code. Some of these keywords are commonly used reserved words; reserved words that do not appear on this list should be capitalized as well.

Capitalize these keywords.








































































The keyword REPLACE is to be used in uppercase only when used as part of the CREATE OR REPLACE clause that is used to create a stored PL/SQL object. Calls to the SQL function replace() should not be presented in uppercase.

In addition to the keywords presented in Table D.2, fully capitalize all of the following:

  The names of all standard exceptions (NO_DATA_FOUND, OTHERS, TOO_MANY_ROWS), and all user-defined exceptions.

  The names of all constants and all user-defined datatypes.

  All acronyms (ANSI, ASCII, HUD, NASA, NOAA, YMCA, and so forth).

  The names of all tables, snapshots, and views, as well as the aliases given to these objects in queries.

  The names of all database triggers.

Use mixed case to refer to the names of user-defined procedures and functions (functions provided by SQL*Plus and PL/SQL are still referenced in lower case). For example:


Optionally, use mixed case for user-defined identifiers. If you choose this method, use capital letters to help make the identifier names more meaningful by visually breaking variable names into words; here are some examples:


All text not handled by these rules should use lowercase. Consider the following:

PROCEDURE My_Sample_Procedure (nParameter1 IN     number,
                               nParameter2    OUT number)
   YES   CONSTANT   char (1) := 'Y';
   IF (some expression) THEN
      replace (vString, chr (9), '   ');
   END IF;
END My_Sample_Procedure;
END My_Sample_Package;

Using PL/SQL Comments

As much as some developers dislike the task, commenting code is essential if the code is going to be maintained. There are a number of steps that can be taken to make comments less necessary:

  Use meaningful identifiers for variables, constants, and parameters. If you use abbreviations to compose identifiers, use the abbreviations consistently (e.g., don’t use both ADDR and ADRS to signify ADDRESS).

  Use the named parameter style of executing procedures and functions. This is especially effective if both the parameters and the variables passed to the stored PL/SQL object have meaningful identifiers.

  Comments about revisions belong in the prologue, not in the body of the module.

TIP:  Commenting Changes And Problem Tracking
If you’re using a problem-tracking system on your project, it’s better to reference a particular report from that system and provide a brief summary of the changes made to solve that problem. Don’t attempt to include all the information about the problem in the prologue; that’s why you bought a problem tracking system!

  Break complex equations and formulas into several smaller statements.

  Reuse existing functions and procedures to accomplish your tasks. Identify code that can be reused.

There are a number of locations in PL/SQL code where comments should almost always be used, including the following instances:

  Before each loop structure.

  Before each BEGIN…END sub-block.

  Before each conditional logic expression (IF <condition> THEN).

  Before any other logically significant statements.

Do not comment each line of code! Only comment important parts of your code, explaining why the code is written in a particular way. Explain business rules if possible. Never use a comment to restate the actions of a piece of code.

PL/SQL supports the following two styles of commenting:

/* We need to determine which students are in academic trouble. */
-- We need to determine which students are in academic trouble.

PL/SQL does not support the nesting of C-style comments; you cannot comment out a C-style comment using other C-style comments. For this reason, it is strongly recommended that only the double-dash (--) style of commenting be used except when commenting out blocks of code.

The exception to this rule is inside 3GL programs that use the Oracle Precompilers. The Oracle Precompilers don’t support single line comments. On these occasions, use the commenting style most appropriate to the 3GL.

If a comment is required, place the comment on the line immediately preceding the line of code. Do not append comments to the end of code; if a comment is warranted by the complexity of the code and you have used meaningful identifiers, the comment should be complicated enough that you need to explain the situation using more than one or two words.


-- Determine which students might be in trouble academically. We want
-- to help them perform better in school.
IF (some condition) THEN


IF (some condition) THEN -- who's got bad grades?

All comments should use proper grammar, punctuation, and spelling. Comments should be complete, coherent sentences.

TIP:  Volume Of Comments
As a general rule, about one-third of your final code should be comments. This figure often varies depending on the size and complexity of the code, but is an excellent rule of thumb.

PL/SQL Indentation

The most important element in readable code is consistent indentation, which illustrates clearly the logic flow of a procedure. Consider these blocks of code:

IF (x < 7) THEN
IF (y < 0) THEN
ELSIF (x > 10) THEN
IF (x < 7) THEN
   IF (y < 0) THEN
   END IF;
ELSIF (x > 10) THEN

Horizontal alignment in the second block of code makes it much easier to follow, even though it is syntactically and functionally identical to the first block of code.

You should not use more than three or four levels of indentation in any block of code. If this many levels of indentation become necessary, consider breaking the code into smaller modules. Too many levels of indentation is almost as bad as no indentation at all.

  Code should always be indented consistently, using three spaces for each level of indentation. Variable, type, and constant declarations should all be indented to the first level of indentation. Do not use tab characters.

  IF (some expression) THEN
     IF (some expression) THEN
        IF (some expression) THEN
        ELSIF (some expression) THEN
        END IF;
     END IF;

  Statements following the WHEN clause of an exception handler should be indented five spaces, in order to create a column-like effect within the exception handler.

          DBMS_Output.Put_Line (SQLERRM);


        DBMS_Output.Put_Line (SQLERRM);

PL/SQL Horizontal Spacing

You’ve probably heard of the obfuscated C contest, where the entrants attempt to cram as much code as possible onto a single line that does some type of work while remaining completely unreadable. If you have any experience maintaining code, you’ve probably seen more than a few pieces of code that have a good chance of winning a contest like this. The guidelines presented in this section are an attempt to guide you away from writing hard-to-read code.

  One of the most important elements in creating readable code is the spacing placed around operators. Table D.3 shows common operators and keywords that need to be preceded and followed by a space when they are used in expressions.

Table D.3 Operators and keywords to be preceded and followed by a space when used in expressions.




























Often more than one of the operators and keywords shown in Table D.3 will be adjacent to each other inside an expression. In this instance, it is recommended that only one space lie between the two operators/identifiers. For example:


  Spaces should precede and follow character (') literals.

  SELECT first_name || ' ' || middle_name || ' ' || last_name
  WHERE  ssn = 999999999;

  Do not leave any blank spaces preceding or following the ** operator.

  nRaisedNum := nNum**nPower;

  Do not leave blank spaces before or after the plus (+) and minus (-) signs when used as unary operators.

  nNumber := -nSecondNumber;
  nNumber := +nSecondNumber;

  Do not use spaces between multiple parentheses or semicolons (;). Always precede the first opening parenthesis of a set with a space.

  AND (((x < 5) AND (y < 5))
  OR   ((x > 5) AND (y > 5)));

PL/SQL Vertical Spacing

Vertical spacing helps distance elements in the code from one another, reducing the visual clutter above and below statements. To create appropriate vertical spacing for your code, place a blank line in the locations described in the following list:

  Before lines containing the keywords IF, ELSE, ELSIF, and EXCEPTION. If the line is preceded by a comment, place the blank line before the comment instead of before the line of text.

  -- If the student's grade point average meets the criteria for
  -- mandatory academic counseling, add the student's name and social
  -- security number to the list.
  IF (nRealGPA < 1.5) THEN
  -- We also want to consider students who are failing two or more
  -- classes, even if their GPA is above 1.5.
  ELSIF Has_Two_Fails (nForSSN => nSSN) THEN

  Before any line containing the LOOP keyword. Do not place a blank line before source code containing the END LOOP keyword. (As with lines of code containing the IF keyword, keep the comments for a line of code with the comment by placing a blank line before the comment.)

  -- For each student returned by the query, add the student's social
  -- security number to the PL/SQL table.
  FOR Students_rec IN Students_cur LOOP

  Before each exception after the first declared within the EXCEPTION section of a PL/SQL block.


  Before and after the variable, constant, and type declarations for a PL/SQL block.

  PROCEDURE Update_Student_GPA (nSSN IN     number)
  END Update_Student_GPA;

  Following the declaration of the procedure and its parameters.

  PROCEDURE Update_Student_GPA (nSSN IN     number)

  Do not place an empty line before a line containing the END IF keyword. Do place blank lines after the last line of code containing the END IF keyword.

  IF (some expression) THEN
    IF (some expression) THEN
      IF (some expression) THEN
      END IF;
    END IF;

PL/SQL Named Notation

Procedures and functions should always be called using named notation for their parameters. This helps identify the data that is being passed to the stored PL/SQL object (assuming that the identifiers chosen for the parameters are meaningful). Place only one parameter on each line of the call:

DaysBetween (dStartDate   => dEnrolledDate,
             dEndDate     => dGraduationDate,
             nGPA         => nFinalGPA,
             nDaysBetween => nDuration);

Statements Per Line And Line Width

Place only one statement (or part thereof) per line of code. Break compound statements over multiple lines. Do not exceed a maximum line width of 80 characters (including indentation). Due to space limitations, the lines in this book are limited to a maximum of 70 characters.

If your code has a complex equation or formula that is expressed using a single statement or operation, consider breaking the code into several smaller statements to make the operations less intimidating. The equation will be much easier to debug; in addition, the process of breaking the equation into subsections will increase your awareness of any mistakes that you make.

PL/SQL Triggers

Database triggers are named using this convention:

table_name + trigger_type_extension

In this example, table_name is the name of the trigger’s base table, and trigger_type_extension represents one of the four types of database triggers listed below, plus one or more of the letters shown in Table D.5 to indicate which DML statements cause the trigger to fire.

The four types of database triggers.


Trigger Type



Before statement


Once, before the DML statement acting on the table.

Before row


Once for each row affected by the DML statement, before the DML statement is executed.

After row


Once for each row affected by the DML statement, after the DML statement is executed.

After statement


Once, after the DML statement has finished executing.



 Modifications to trigger_type_extension to indicate DML statements handled by the trigger.


Trigger Type Extension

DML Statement


The trigger fires when a DELETE statement modifies the base table.


The trigger fires when an INSERT statement modifies the base table.


The trigger fires when an UPDATE statement modifies the base table.



Thus, a BEFORE INSERT or UPDATE row level trigger on the STUDENTS table would be named STUDENTS_BRIU.

If the length of the trigger name exceeds 30 characters when following this standard, abbreviate the name of the trigger’s base table to create the name of the trigger. Under no circumstances should the trigger type extension be abbreviated.

PL/SQL Identifiers

When declaring variables and constants, the developer should preface a meaningful identifier with one of the prefixes shown in below.

Datatype prefixes for use in identifiers.














































Identifiers should always use mixed-case and capital letters to indicate separation of elements within an identifier. Thus, a variable of type varchar2 that holds a student’s first name would be vStudentFirstName.

The identifiers used for explicitly declared cursors should be meaningful; the suffix _cur should be appended to the identifier. For example:

CURSOR Students_cur
SELECT first_name, middle_name, last_name, overall_gpa, most_recent_gpa

Identifiers declared using %TYPE should still include a datatype prefix as part of the identifier name:

nStudentSSN    STUDENTS.ssn%TYPE;

Identifiers declared using %ROWTYPE should be named like the object that is lending the variable its structure. These identifiers should always include the _rec suffix as part of the identifier:

Students_rec          STUDENTS%ROWTYPE;
FailingStudents_rec   FailingStudents_cur%ROWTYPE;

PL/SQL Procedures and Functions standards

Stored procedures and functions should be named first by the type of action the object performs and then by the object of that action. For instance, a procedure that calculates interest on a student’s remaining balance would be named Calculate_Balance_Interest.

PL/SQL Packages

Packages should be named in accordance with the general purpose of the procedures and functions contained within the package. For instance, a package containing routines used to calculate a student’s GPA would be named GPA_Calculations.

All documentation pertaining to system design should follow the conventions identified in this standard. This applies especially to pseudocode that is used to document stored PL/SQL objects.

In addition, all written documentation should reference calls to built-in and developer-written procedures and functions using a set of parentheses, as in “will call the Calculate_Semester_GPA() procedure”. The names of built-in and developer-written code modules should also be referenced in bold.

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

Also see:


If you like Oracle tuning, see the 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.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter