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 )

- 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.