Engineering Better PL/SQL
PL/SQL is a great language. It’s relatively simple to learn, is well integrated with the
Oracle database, and can often be the most efficient way to perform complex or large
scale database operations. In fact PL/SQL is so useful, it’s difficult to believe that its
origin is SQL*Forms – and that PL/SQL was once was an optional cost add-on to the
database. Working with Oracle over the past few decades has come a long way, and
PL/SQL had evolved into a mature, robust and highly functional database language.
However contrary to logical and reasonable expectations, a simple to learn yet robust
language like PL/SQL does not automatically guarantee programs which are readable,
maintainable, effective (i.e. correct) and efficient. In fact, some of the worst programs
I’ve seen over the past twenty years of Oracle development were written in PL/SQL. I
have often been quite amazed at just how easily one can “shoot themselves in the foot”
with PL/SQL – and how often it goes undetected until a major production crisis occurs.
So the question is “How do we engineer better PL/SQL?” This paper will examine some
commonly used manual methods and their shortcomings, and then will offer some more
scientific advice for how to improve upon the PL/SQL development process. And while
this paper will demonstrate techniques using Quest Software’s TOAD for Oracle product,
the practices espoused within are actually based on industry standards (although as of yet
not universally prevalent – although hoping that papers like this may help to correct that).
The Cost of Software Defects
In order to see the full value for engineering better PL/SQL, we must first understand and
appreciate the ramifications for not doing so. Thus looking beyond the simple yet highly
relevant fact that our jobs may well depend on it, what does poorly engineered code cost
these days? The answers are quite staggering. One survey estimates that inferior software
engineering and inadequate testing in 2002 alone c