PL/SQL Best Practices
The primary purpose of PL/SQL coding
standards has always been to make maintenance easier for developers.
PL/SQL best practices make the job even easier. In order to satisfy
the requirement for making maintenance easier for the developers, PL/SQL coding standards must
address several areas of the development process. PL/SQL best
practices take the implementation of coding standards to a higher
See my notes on PL/SQL best practices:
Jay Singh posted a great set of PL/SQL
best practices and coding guidelines on Oracle-L:
The following are the eight
guidelines for PL/SQL best practices, according to Jay:
1) While writing PL/SQL, use
the correct datatype so that implicit conversion will be avoided
2) Use bind variable to avoid
3) Use BULK COLLECT, %
ATTRIBUTES wherever required
Write the code that fit into
your screen size.
refinement, you can reduce a complex problem to a
set of simple problems that
have easy-to-implement solutions.
5) EXCEPTION WHEN OTHERS is
almost always a BUG unless it is immediately
followed by a RAISE. Use
WHEN OTHERS exception as the last resort and handle exceptions.
WHEN OTHERS THEN
if (sqlcode=-54) then
.... deal with it.
6) Tom's Mantra
If (possible in SQL)
else if(possible in PL/SQL)
else if(possible in JAVA)
7) % ATTRIBUTES
Use %TYPE and %ROWTYPE
attributes. No code change is required when schema structure
8) BEFORE VS AFTER TRIGGER
NEVER USE BEFORE TRIGGER FOR
VALIDATIONS. Use BEFORE triggers ONLY to modify :NEW value.
Oracle Documentation States:
"BEFORE row triggers are slightly more efficient than
AFTER row triggers. With
triggers, affected data blocks must be read (logical read, not
physical read) once for the trigger and then again for the
triggering statement. Alternatively, with
triggers, the data blocks must be read only once for both the
triggering statement and the trigger."