Oracle Coding Best Practices

/

Overview

This document defines a list of standards and guidelines that will be used when submitting PL/SQL Components. Good standards do not constrain the creativity of designers and developers, but rather encourage the development of best practices.  

  • Packages and Procedure

All PL/SQL functions and procedures will be implemented as part of a package.

  • PL/SQL Header Comments

A header should appear at the start of any script, procedure, function, package body, or package spec. A header is the most essential documentation for any piece of stored code. At no other point in the code are provisions made for documenting the overall purpose, logic, and interface of a module than in the header comments. Consider this template header:

      --********************************************************************** 
      -- Filename: STMT_RRD_PKG.sql
      -- Project : Global Wealth Management Platform (Statements)
      -- Copyright (c) 2006, ABC, Inc. All rights reserved. 
      --
      -- Description: Describe the purpose of the object. If necessary,
      -- describe the design of the object at a very high level.

      -- Revision History
      -- Date           Author      Reason for Change
      -- ----------------------------------------------------------------
      -- 21 Aug 2012    Gary k  Baseline.

      /**********************************************************************
  • Exception Handling

Exception handling code will catch specific exceptions and will make every effort to preserve the root cause of exceptions to the client code.Use WHEN OTHERS exception as the last resort and handle exceptions.

For example:

            EXCEPTION
            WHEN OTHERS THEN
                if (sqlcode=-54) then
                    .... deal with it.
            else
            RAISE;
  • Bind Variables

Bind variables (sometimes called Prepared Statements or Parameter Markers) will  be used whenever possible. All non-dynamic SQL statements will use bind variables.

  • Row and Column Types

When there is a direct correlation between a variable and a table column, the %TYPE or %ROWTYPE will be used.  No code change is required when schema structure changes.

            Example: 
             DECLARE 
             l_account_name account.account_name%TYPE;
  • Include the SET condition in the WHERE clause

Simply including the existing state of the SET clause can result in a huge performance improvement for UPDATE statements:

    -- million row update

       UPDATE TRANSACTION SET FLAG=0 WHERE CATEGORY='X'

-- hundred row update

     UPDATE TRANSACTION SET FLAG=0 WHERE CATEGORY='X' AND FLAG!=0

Including the SET clause will help reduce  high redo waits (log file parallel write waits, log file sync waits).  

  • Coding Mantra
                    If (possible in SQL)
                       do it;
                    else if(possible in PL/SQL)
                       do it;
                    else if(possible in JAVA)
                       do it;
                    else
                        ..
                        ..
                     end if;
  • Optimize SQL in PL/SQL programs

From BULK COLLECT to FORALL, it’s important we take advantage of key special features in PL/SQL to execute SQL, and also control how SQL is written in applications. The idea is to avoid context switch between SQL and PL/SQL engine.

( Photo Credit : Steven Feuerstein )

lat.png
  • Complex Queries

Re-write complex queries with temporary tables and SQL With Clause. Oracle created GTT's and With Clause to divide and conquer complex queries.

  • ANSI SQL

    All new Oracle SQL should be in ANSI SQL.

  • Transaction Control

Transactions will generally be managed by the calling component.

  • We write code once but read it many times

PL/SQL is a functional language which is very readable. We should make every effort to make the code cleaner and maintainable.

 






/ / Comment
,

Girish Kumar