Buy this Book
PDF $13.99
Add to Cart
Read it Now!
Reprint Licensing
Envelope Tell a friend

Oracle PL/SQL Best Practices
Oracle PL/SQL Best Practices Optimizing Oracle Code

By Steven Feuerstein

Cover | Table of Contents | Colophon

Table of Contents

Chapter 1: The Development Process
Content preview·Buy reprint rights for this chapter
To do your job well, you need to be aware of, and to follow, both "little" best practices—very focused tips on a particular coding technique—and "big" best practices. This chapter offers some suggestions on the big picture: how to write your code as part of a high-quality development process.
My objective isn't to "sell" you on any particular development methodology (though I must admit that I am most attracted to so-called "lightweight" methodologies such as Extreme Programming and SCRUM). Instead, I'll remind you of basic processes you should follow within any big-picture methodology.
In other words, if you (or your methodology) don't follow some form of the best practices in this chapter, you are less likely to produce high-quality, successful software. I don't (with perhaps a single exception) suggest a specific path or tool. You just need to make sure you've got these bases covered.
DEV-01: Set standards and guidelines before writing any code.
These standards and guidelines would, if I had my way, include many or all of the best practices described in this book. Of course, you need to make your own decisions about what is most important and practical in your own particular environment.
Key areas of development for which you should proactively set standards are:
  • Selection of development tools : You should no longer be relying on SQL*Plus to compile, execute, and test code; on a basic editor like Notepad to write the code; or on EXPLAIN PLAN to analyze application performance. Software companies offer a multitude of tools (with a wide range of functionality and price) that will help dramatically improve your development environment. Decide on the tools to be used by all members of the development group.
  • How SQL is written in PL/SQL code : The SQL in your application can be the Achilles' heel of your code base. If you aren't careful about how you place SQL statements in your PL/SQL code, you'll end up with applications that are difficult to optimize, debug, and manage over time.
  • An exception handling architecture
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Chapter 2: Coding Style and Conventions
Content preview·Buy reprint rights for this chapter
Software developers are a very privileged bunch. We don't have to work in dangerous environments, and our jobs aren't physically taxing (though carpal tunnel syndrome is always a threat). We are paid to think about things, and then to write down our thoughts in the form of code. This code is then used and maintained by others, sometimes for decades.
Given this situation, I believe we all have a responsibility to write code that can be easily understood and maintained (and, c'mon, let's admit our secret desires, admired) by developers who follow in our footsteps.
Steve McConnell's http://www.construx.com site, along with his book, Code Complete (Microsoft Press), offers checklists on coding style, naming conventions and rules, and module definitions.
STYL-01: Adopt a consistent, readable format that is easy to maintain.
Your code should have a "signature," a style that is consistent (all your programs look the same), readable (anyone can pick up your code and make sense of it), and maintainable (a minor change in the code shouldn't require 15 minutes of reformatting).
Ideally, everyone in your organization would adopt a similar style, so that everyone can easily understand everyone else's code. This can be tricky, as programmers sometimes take a dogmatic approach to such issues as size of indentation and use of whitespace.
You have two options regarding coding style:
  • Find or write a set of guidelines, and then try as hard as you can to follow (and get your group to follow) those guidelines. See Section for a sample document.
  • Use a tool to automatically format your code for you. The dominant code formatter for PL/SQL is currently PL/Formatter from RevealNet (see Section ). This product is not only available standalone, but is also integrated into many popular integrated development environments (IDEs).
I strongly recommend that you use PL/Formatter or some other "pretty print" tool. It is quite liberating to write code without any concern whatsoever for how it looks: I focus completely on the logical flow and then press a button a moment later to turn it into readable, attractive code.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Chapter 3: Variables and Data Structures
Content preview·Buy reprint rights for this chapter
PL/SQL is a strongly typed language. This means that before you can work with any kind of data structure, you must first declare it. And when you declare it, you specify its type and, optionally, an initial or default value. All declarations of these variables must be made in the declaration section of your anonymous block, procedure, function, or package.
Use the best practices described in this section when you declare your data structures.
DAT-01: Match datatypes to computational usage.
Gee, that's a general best practice, isn't it? Of course you should do things the right way. So the question becomes: what datatype is the correct datatype? The following table offers some concrete advice on potential issues you might encounter:
Datatype
Issues and Recommendations
NUMBER
If you don't specify a precision, as in NUMBER(12,2), Oracle supports up to 38 digits of precision. If you don't need this precision, you're wasting memory.
CHAR
This is a fixed-length character string and is mostly available for compatibility purposes with code written in earlier versions of Oracle. The values assigned to CHAR variables are right-padded with spaces, which can result in unexpected behavior. Avoid CHAR unless it's specifically needed.
VARCHAR
This variation on the VARCHAR2 variable-length declaration is provided by Oracle for compatibility purposes. Eschew VARCHAR in favor of VARCHAR2.
VARCHAR2
The greatest challenge you will run into with VARCHAR2 is to avoid the tendency to hard-code a maximum length, as in VARCHAR2(30). Use %TYPE and SUBTYPE instead, as described later in this chapter.
Also, prior to Oracle8, VARCHAR2 variables are treated like variable-length strings for purposes of manipulation and evaluation, but Oracle does allocate the full amount of memory upon declaration. If you declare a variable of VARCHAR2(2000), then Oracle allocates 2000 bytes, even if you use only three.
INTEGER
If your integer values fall within the range of –231+1 .. 231–1 (a.k.a. –2147483647 .. 2147483647), you should declare your variables as PLS_INTEGER. This is the most efficient format for integer manipulation.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Declaring Variables and Data Structures
Content preview·Buy reprint rights for this chapter
Use the best practices described in this section when you declare your data structures.
DAT-01: Match datatypes to computational usage.
Gee, that's a general best practice, isn't it? Of course you should do things the right way. So the question becomes: what datatype is the correct datatype? The following table offers some concrete advice on potential issues you might encounter:
Datatype
Issues and Recommendations
NUMBER
If you don't specify a precision, as in NUMBER(12,2), Oracle supports up to 38 digits of precision. If you don't need this precision, you're wasting memory.
CHAR
This is a fixed-length character string and is mostly available for compatibility purposes with code written in earlier versions of Oracle. The values assigned to CHAR variables are right-padded with spaces, which can result in unexpected behavior. Avoid CHAR unless it's specifically needed.
VARCHAR
This variation on the VARCHAR2 variable-length declaration is provided by Oracle for compatibility purposes. Eschew VARCHAR in favor of VARCHAR2.
VARCHAR2
The greatest challenge you will run into with VARCHAR2 is to avoid the tendency to hard-code a maximum length, as in VARCHAR2(30). Use %TYPE and SUBTYPE instead, as described later in this chapter.
Also, prior to Oracle8, VARCHAR2 variables are treated like variable-length strings for purposes of manipulation and evaluation, but Oracle does allocate the full amount of memory upon declaration. If you declare a variable of VARCHAR2(2000), then Oracle allocates 2000 bytes, even if you use only three.
INTEGER
If your integer values fall within the range of –231+1 .. 231–1 (a.k.a. –2147483647 .. 2147483647), you should declare your variables as PLS_INTEGER. This is the most efficient format for integer manipulation.
DAT-02: Anchor variables to database datatypes using %TYPE and %ROWTYPE.
When you declare a variable using %TYPE or %ROWTYPE, you "anchor" the type of that data to another, previously defined element. If your program variable has the same datatype as (and, as is usually the case, is acting as a container for) a column in a table or view, use %TYPE to define it from that column. If your record has the same structure as a row in a table or view, use %ROWTYPE to define it from that table.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Using Variables and Data Structures
Content preview·Buy reprint rights for this chapter
Use the best practices described in this section when you reference the data structures you have declared in your programs.
DAT-07: Replace complex expressions with Boolean variables and functions.
A Boolean expression evaluates to one of three values: TRUE, FALSE, or NULL. You can use Boolean variables and functions to hide complex expressions; the result is code that is virtually as readable as "straight" English—or whatever language you use to communicate with other human beings.
Example
IF total_sal BETWEEN 10000 AND 50000 AND
   emp_status (emp_rec.empno) = 'N' AND
   (MONTHS_BETWEEN 
      (emp_rec.hiredate, SYSDATE) > 10)
THEN
   give_raise (emp_rec.empno);
END IF;
Wow, that's hard to understand! It'd be much easier if the code looked like this: 
IF eligible_for_raise (totsal, emp_rec)
THEN
   give_raise (emp_rec.empno);
END IF;
And even if you don't want to (or need to) bother with creating a separate function, you can still move the complexity to a local variable, as in: 
DECLARE
  eligible_for_raise BOOLEAN :=
    total_sal BETWEEN 10000 AND 50000 AND
      emp_status (emp_rec.empno) = 'N' AND
      (MONTHS_BETWEEN 
         (emp_rec.hiredate, SYSDATE) > 10);
BEGIN
   IF eligible_for_raise 
   THEN
      give_raise (emp_rec.empno);
   END IF;
Benefits
It will be much easier for anyone to read your code; you can literally read it. If you then need to understand how the Boolean expression is computed, you can look "under the covers."
This is a technique that can be applied (with care) to existing "spaghetti code." As you go into a program to fix or enhance it, look for opportunities to simplify and shorten executable sections by shifting complexity to local variables and programs.
Challenges
Before you modify existing code, make sure you have solid unit test scripts in place so you can quickly verify that your changes haven't introduced bugs into the program.
Resources
http://oracle.oreilly.com/utplsql: utPLSQL, a unit test framework for PL/SQL developers.
DAT-08: Do not overload data structure usage.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Declaring and Using Package Variables
Content preview·Buy reprint rights for this chapter
Use the best practices described in this section when you are declaring variables for use in packages.
DAT-12: Package application-named literal constants together.
Never place a hard-coded literal, such as "Y" or 150 in your code. Instead, create a package to hold these values and publish a name to be used in place of the literals. You will probably find it best to:
  • Define constants that are referenced throughout your application in a single, central package.
  • Define constants that are more specific to a single area of functionality within the package that encapsulates that functionality.
Example
Here is a portion of a general constants package:
CREATE OR REPLACE PACKAGE constants
IS
   -- Standard string representation of TRUE/FALSE
   tval CONSTANT CHAR(1) := 'T';
   fval CONSTANT CHAR(1) := 'F';

   -- Earliest valid date: 5 years past
   min_date CONSTANT DATE := 
      ADD_MONTHS (SYSDATE, -5 * 12);
And here is a package that contains constants specific to one area of functionality: 
CREATE OR REPLACE PACKAGE nightly_transform
IS
   c_max_weeks CONSTANT INTEGER := 54;

   c_active CONSTANT CHAR(1) := 'A';
   c_inactive CONSTANT CHAR(1) := 'I';

   c_english CONSTANT INTEGER := 1;
   c_usa CONSTANT INTEGER := 1;
   c_namerica CONSTANT VARCHAR2(2) := 'N';
END nightly_transform;
Benefits
You're less likely to hard-code literal values in your programs, thus improving the readability and maintainability of your code.
Youve established a place to go when a developer needs to add another constant to hide a literal.
Challenges
The entire development team needs to know about the packages and use the constants that have been defined for them.
Be careful about the values you assign to your constants. With cut-and-paste, it's easy to end up assigning a value that's too long and raises the "ORA-06502: PL/SQL: numeric or value error" at runtime—when the package is initialized.
DAT-13: Centralize TYPE definitions in package specifications.
As you use more and more of the PL/SQL language features, you will define many TYPEs of things, including:
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Chapter 4: Control Structures
Content preview·Buy reprint rights for this chapter
Oracle PL/SQL offers a range of constructs that allow you to control the flow of processing, including:
  • For conditional logic: the IF statement
  • For loop processing: FOR, WHILE, and simple loops
  • For branching logic: the GOTO statement
These constructs are relatively straightforward in syntax and usage. There remain, however, several best practices you should take into account when you work with these kinds of statements.
Follow the best practices in this section when you are using PL/SQL's IF statements.
CTL-01: Use ELSIF with mutually exclusive clauses.
When you need to write conditional logic that has several mutually exclusive clauses (in other words, if one clause is TRUE, no other clause evaluates to TRUE), use the ELSIF construct: 
IF condA THEN
   ...
ELSIF condB THEN
   ...
ELSIF condN THEN
   ...
ELSE
   ...
END IF;
Example
At first glance, the following procedure makes sense, but on closer examination, it's a mess: 
PROCEDURE process_lineitem (line_in IN INTEGER)
IS
BEGIN
   IF line_in = 1
   THEN
      process_line1;
   END IF;
   IF line_in = 2
   THEN
      process_line2;
   END IF;
   ...
   IF line_in = 2045
   THEN
      process_line2045;
   END IF;
END;
Every IF statement is executed and each condition evaluated. You should rewrite such logic as follows: 
PROCEDURE process_lineitem (line_in IN INTEGER)
IS
BEGIN
   IF line_in = 1
   THEN
      process_line1;
   ELSIF line_in = 2
   THEN
      process_line2;
   ...
   ELSIF line_in = 2045
   THEN
      process_line2045;
   END IF;
END;
Benefits
This structure clearly expresses the underlying "reality" of your business logic: if one condition is TRUE, no others can be TRUE.
ELSIF offers the most efficient implementation for processing mutually exclusive clauses. When one clause evaluates to TRUE, all subsequent clauses are ignored.
CTL-02: Use IF...ELSIF only to test a single, simple condition.
The real world is very complicated; the software we write is supposed to map those complexities into applications. The result is that we often end up needing to deal with convoluted logical expressions.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Conditional and Boolean Logic
Content preview·Buy reprint rights for this chapter
Follow the best practices in this section when you are using PL/SQL's IF statements.
CTL-01: Use ELSIF with mutually exclusive clauses.
When you need to write conditional logic that has several mutually exclusive clauses (in other words, if one clause is TRUE, no other clause evaluates to TRUE), use the ELSIF construct: 
IF condA THEN
   ...
ELSIF condB THEN
   ...
ELSIF condN THEN
   ...
ELSE
   ...
END IF;
Example
At first glance, the following procedure makes sense, but on closer examination, it's a mess: 
PROCEDURE process_lineitem (line_in IN INTEGER)
IS
BEGIN
   IF line_in = 1
   THEN
      process_line1;
   END IF;
   IF line_in = 2
   THEN
      process_line2;
   END IF;
   ...
   IF line_in = 2045
   THEN
      process_line2045;
   END IF;
END;
Every IF statement is executed and each condition evaluated. You should rewrite such logic as follows: 
PROCEDURE process_lineitem (line_in IN INTEGER)
IS
BEGIN
   IF line_in = 1
   THEN
      process_line1;
   ELSIF line_in = 2
   THEN
      process_line2;
   ...
   ELSIF line_in = 2045
   THEN
      process_line2045;
   END IF;
END;
Benefits
This structure clearly expresses the underlying "reality" of your business logic: if one condition is TRUE, no others can be TRUE.
ELSIF offers the most efficient implementation for processing mutually exclusive clauses. When one clause evaluates to TRUE, all subsequent clauses are ignored.
CTL-02: Use IF...ELSIF only to test a single, simple condition.
The real world is very complicated; the software we write is supposed to map those complexities into applications. The result is that we often end up needing to deal with convoluted logical expressions.
You should write your IF statements in such a way as to keep them as straightforward and understandable as possible. For example, expressions are often more readable and understandable when they are stated in a positive form. Consequently, you are probably better off avoiding the NOT operator in conditional expressions.
Example
It's not at all uncommon to write or maintain code that's structured like this:
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Loop Processing
Content preview·Buy reprint rights for this chapter
Follow the best practices in this section when you are using PL/SQL's looping statements.
CTL-04: Never EXIT or RETURN from WHILE and FOR loops.
The WHILE and FOR loops include "boundary conditions" that determine:
  • When and if a loop should execute at all
  • When a loop should stop executing
If you use the EXIT or RETURN statements inside a WHILE or FOR loop, you cause an unstructured termination from the loop. The resulting code is hard to trace and debug.
Example
Here's the bottom half of a function that scans the contents of a collection and returns the row in which a match is found. 
   l_count := titles.COUNT;
   FOR indx IN 1 .. l_rowcount 
   LOOP
      IF l_match_against = titles(indx)
      THEN
         RETURN indx;
      END IF;
   END LOOP;

   RAISE Exit_Function;
EXCEPTION
   WHEN Exit_Function THEN RETURN NULL;
END;
Now this is some nasty code. You manage to get all the way down to the end of the executable section, and you are punished with an exception! See [MOD-07: Limit functions to a single RETURN statement in the execution section.] for how this violates best practice for a "funnel-shaped" function.
Of course, you're not supposed to get to the end of the function. Instead, the function finds a match and zooms straight out of the function with a RETURN.
Now imagine a function whose body is 200 lines long with nested loops and several different RETURNs in different parts of the loop. Chaos!
Benefits
By following the maxim "one way in and one way out" for your loops, the resulting code is much easier to understand and debug. If your loop needs to execute at least once (like a Pascal REPEAT statement), you're better off using a simple LOOP construct and testing for the exit condition with EXIT WHEN.
Challenges
Your exit test in the WHILE expression can become a bit more complex, especially when you have to replace a natural FOR loop with a more mechanical WHILE loop. For example, you have a FOR loop expression that iterates over nested_table.FIRST to nested_table.LAST, but you need to terminate the loop when you find a matching entry. In order to put the exit test in the iteration scheme, you have to now use a WHILE loop, initialize and maintain a loop control variable yourself (for the current offset), and test for the exit condition in the WHILE expression.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Miscellaneous
Content preview·Buy reprint rights for this chapter
The best practices in this section are grouped together simply because they don't fall into either of the other categories.
CTL-10: Use anonymous blocks within IF statements to conserve resources.
One of the nice things about PL/SQL is that you, the developer, can define any set of executable statements as a distinct block, with its own declaration, executable, and exception sections.
If you notice that certain operations and data structures aren't needed unless a certain condition is satisfied, move all the execution of those operations and the declaration of those data structures inside the conditional statement. The result is that you won't incur the overhead (CPU or memory) unless it's absolutely needed.
Example
In the following block, I declare a set of local variables and even initialize l_name with a function that usually takes 10 seconds to execute (min_balance_account). But when I write my block, it turns out that in many situations, those structures are ignored: 
DECLARE
   TYPE account_tabtype IS TABLE 
      OF account%ROWTYPE INDEX BY BINARY_INTEGER;
   l_accounts account_tabtype;

   l_name VARCHAR2(2000) := 
      min_balance_account (SYSDATE);
BEGIN 
   IF balance_too_low (1056)
   THEN
      use_collection (l_accounts);
      use_name (l_name);
   ELSE
      -- No use of l_accounts or l_name
      ...
   END IF;
END;
Once I recognize this situation (usually identified through a code walkthrough), I should change it to this: 
BEGIN
   IF balance_too_low (1056)
   THEN  
      DECLARE  
         TYPE account_tabtype IS TABLE 
            OF account%ROWTYPE 
            INDEX BY BINARY_INTEGER;
         l_accounts account_tabtype;

         l_name VARCHAR2(2000) := 
            min_balance_account (SYSDATE);
      BEGIN
         use_collection (l_accounts);
         use_name (l_name);
      END;
   ELSE
      -- No use of l_accounts or l_name
      ...
   END IF;
END;
Benefits
Your programs won't execute unnecessary code, improving performance and reducing memory requirements for the program.
Challenges
It can be hard to realize as you first write your program that this kind of situation exists. Use code walkthroughs to uncover these optimization opportunities. You can also use Oracle8
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Chapter 5: Exception Handling
Content preview·Buy reprint rights for this chapter
Even if you write such amazing code that it contains no errors and never acts inappropriately, your users might still use your program incorrectly. The result? Situations that cause programs to fail. PL/SQL provides exceptions, a flexible and powerful architecture that raises, traps, and handles errors.
Before getting into specific best practices, you should be sure to understand how exception handling works. For example, remember that an exception section handles only errors raised in the executable section of the block, not errors raised in the declaration section.
Next and even more important, I offer the following meta-best practice of this chapter.
EXC-00: Set guidelines for application-wide error handling before you start coding.
It's impractical to define EXCEPTION sections in your code after the fact—in other words, after the programs have been written. The best way to implement application-wide, consistent error handling is to use a standardized package that contains at least the following elements:
  • Procedures that perform most exception-handling tasks, such as writing to an error log.
  • A raise program that hides the complexity of RAISE_APPLICATION_ERROR and application-specific error numbers.
  • A function that returns error message text for a given error number.
These ideas are covered in specific best practices in this chapter. A simple error-handling package may be found in the err.pkg file on the Oracle PL/SQL Best Practices web site.
The following best practices cover how to check for conditions that might require the raising of an exception, deciding how to propagate exception information, and how to best raise exceptions.
EXC-01: Verify preconditions using standardized assertion routines that raise violation exceptions.
Every time you write a program, you make certain assumptions. A user of your program doesn't necessarily know about those assumptions. If you don't "code defensively" and make sure that your assumptions aren't violated, your programs can break down in unpredictable ways.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
EXC-00: Set guidelines for application-wide error handling before you start coding.
Content preview·Buy reprint rights for this chapter
EXC-00: Set guidelines for application-wide error handling before you start coding.
It's impractical to define EXCEPTION sections in your code after the fact—in other words, after the programs have been written. The best way to implement application-wide, consistent error handling is to use a standardized package that contains at least the following elements:
  • Procedures that perform most exception-handling tasks, such as writing to an error log.
  • A raise program that hides the complexity of RAISE_APPLICATION_ERROR and application-specific error numbers.
  • A function that returns error message text for a given error number.
These ideas are covered in specific best practices in this chapter. A simple error-handling package may be found in the err.pkg file on the Oracle PL/SQL Best Practices web site.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Raising Exceptions
Content preview·Buy reprint rights for this chapter
The following best practices cover how to check for conditions that might require the raising of an exception, deciding how to propagate exception information, and how to best raise exceptions.
EXC-01: Verify preconditions using standardized assertion routines that raise violation exceptions.
Every time you write a program, you make certain assumptions. A user of your program doesn't necessarily know about those assumptions. If you don't "code defensively" and make sure that your assumptions aren't violated, your programs can break down in unpredictable ways.
Use assertion routines to make it as easy as possible to validate assumptions in a declarative fashion. These routines, standardized for an entire application, take care of all the housekeeping: what to do when a condition fails, how to report the problem, and whether and how to stop the program from continuing.
Example
Here's a simple assertion program that checks to see if a condition is TRUE. If the condition is FALSE or NULL, the procedure displays a message to the screen and then raises an exception (if so desired) with dynamic PL/SQL (this implementation relies on Oracle8i `s native dynamic SQL): 
CREATE OR REPLACE PROCEDURE assert (
   condition_in IN BOOLEAN,
   message_in IN VARCHAR2,
   raise_exception_in IN BOOLEAN := TRUE,
   exception_in IN VARCHAR2
         := 'VALUE_ERROR'
)
IS
BEGIN
   IF    NOT condition_in
      OR condition_in IS NULL
   THEN
      pl ('Assertion Failure!');
      pl (message_in);

      IF raise_exception_in
      THEN
         EXECUTE IMMEDIATE 
           'BEGIN RAISE ' || exception_in || '; END;';
      END IF;
   END IF;
END assert;
With this program in place, you can easily, and in a declarative fashion, make sure that all inputs are hunky-dory before proceeding with your business logic. Here's an example: 
BEGIN
   assert (isbn_in IS NOT NULL,
     'The ISBN must be provided.');

   assert (page_count_in < 2000,
     'Readers don't like big, fat books!');
Benefits
With easy-to-use, declarative assertion routines, you're more likely to actually check for valid inputs and conditions.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Handling Exceptions
Content preview·Buy reprint rights for this chapter
Once an exception is raised, it generally needs to be handled. These best practices offer advice on writing exception-handling sections.
EXC-07: Handle exceptions that cannot be avoided but can be anticipated.
If you are writing a program in which you can predict that a certain error will occur, you should include a handler in your code for that, allowing for a graceful and informative failure.
The form that this failure takes doesn't, by the way, necessarily need to be an exception. When writing functions, you may well decide that in the case of certain exceptions, you will want to return a value such as NULL, rather than allow an exception to propagate out of the function.
Example
This recommendation is easily demonstrated with the ubiquitous SELECT INTO lookup query. An error that often occurs is NO_DATA_FOUND, which indicates that the query didn't identify any rows. Now, following [SQL-04: Put single-row fetches inside functions; never hard-code a query in your block.], I put SELECT INTO inside a function, but I don't allow the NO_DATA_FOUND exception to propagate out of the function: 
CREATE OR REPLACE FUNCTION book_title (
   isbn_in IN book.isbn%TYPE)
RETURN book.title%TYPE
IS
   l_ title book.title%TYPE;
BEGIN
   SELECT title INTO l_title   
     FROM book                 
    WHERE isbn =isbn_in; 
   RETURN l_rec.title;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      RETURN NULL;
END;
In other words, if the ISBN passed to the function finds no book, return NULL for the title. This is an unambiguous indicator of failure; a book must have a title.
I have decided in this case not to allow NO_DATA_FOUND to propagate (go unhandled) out of the function. I use a SELECT INTO (implicit query) to fetch the book title; Oracle's implementation of implicit queries means that NO_DATA_FOUND (as well as TOO_MANY_ROWS) might be raised. That doesn't mean, however, that within my function, it really is an exception when no row is found. In fact, I might be expecting to not find a match.
By returning NULL rather than propagating an exception, I leave it up to the user of my function to decide how to deal with a "no row found" situation. She might raise an exception, as in:
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Declaring Exceptions
Content preview·Buy reprint rights for this chapter
In addition to raising and handling exceptions, you also must pay attention to how and when to declare exceptions and to assign names to error numbers.
EXC-12: Standardize named application exceptions in package specifications.
It's likely that a developer will raise a certain error or errors in the process of using your code, you should declare exceptions in the package specification. Users of your code can then trap and handle those errors by name.
This approach is used most often for application-specific exceptions, but if your program might also raise an Oracle exception that has not been given a name in the STANDARD or other built-in package, you can give it a name and associate it with that number. See [EXC-14: Use the EXCEPTION_INIT pragma to name system exceptions that might be raised by your program.] for more details.
Example
Suppose that my overdue.analyze_status procedure might raise one of the following two errors:
"Overdue more than one month"
I have defined this as a serious error in my database. I must immediately stop processing and raise an exception.
"Fetch out of sequence"
This is an Oracle error that occurs when something goes wrong in my cursor FOR loop.
I then add these lines to my overdue package:
CREATE OR REPLACE PACKAGE overdue
IS
   excessive_lateness EXCEPTION;
   PRAGMA EXCEPTION_INIT (
      excessive_lateness, -20700);

   fetch_out_of_sequence EXCEPTION;
   PRAGMA EXCEPTION_INIT (
      fetch_out_of_sequence, -1003);
Benefits
Programmers have a better sense of what to expect—and what kind of exception handlers to write—when using your code.
Resources
sqlerr.pks: Package of predefined exceptions that commonly occur when working with SQL, and especially dynamic SQL, inside PL/SQL.
EXC-13: Document all package exceptions by module in package specifications.
Different programs may well raise different exceptions. You need to communicate this information clearly to users of your code so they know what to expect and what to code for. PL/SQL doesn't offer a structured way to do this as part of the language ( Java, for example, does precisely that). So you need to come up with a standard convention for including such documentation in your code.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Chapter 6: Writing SQL in PL/SQL
Content preview·Buy reprint rights for this chapter
One of the reasons developers like PL/SQL so much is that it's so easy to write SQL inside a PL/SQL block of code.
One of the most dangerous aspects of PL/SQL is that it's so easy to write SQL inside a PL/SQL block of code.
Paradox? Irony? SQL is, in fact, a sort of Achilles heel of PL/SQL development. Now, given that PL/SQL was first conceived as a procedural language extension to SQL, such a statement should raise eyebrows even further. The simple fact of the matter, however, is that if you aren't careful about how you place SQL statements in your PL/SQL code, you will end up with applications that are difficult to optimize, debug, and manage over time.
You should follow several simple (to state) guidelines when working with SQL inside PL/SQL. I collect all of these together in the following meta-best practice of this chapter.
SQL-00: Establish and follow clear rules for how to write SQL in your application.
  • Never repeat a SQL statement.
  • Encapsulate all SQL statements behind a procedural interface (usually a package).
  • Write your code assuming that the underlying data structures will change.
  • Take advantage of PL/SQL-specific enhancements for SQL.
All these topics—with examples, benefits, and challenges—are explored in the more detailed best practices in this chapter.
This section contains some general-purpose best practices for writing SQL statements and some specific best practices for handling transactions.
SQL-01: Qualify PL/SQL variables with their scope names when referenced inside SQL statements.
You could declare a variable that has the same name as a table, a column, or a view. The PL/SQL compiler won't get confused, but you might, and your SQL statements inside PL/SQL might not work as intended. So you should always make sure that there is no ambiguity between SQL and PL/SQL identifiers. The best way to do this is to qualify all references to PL/SQL variables with their scope name.
Example
Consider the following block:
CREATE OR REPLACE PROCEDURE show_fav_flavor (
   pref_type IN VARCHAR2)
IS
   pref VARCHAR2(100);
BEGIN
   SELECT preference INTO pref
     FROM personal_preferences PP
    WHERE PP.pref_type = pref_type;
   pl (pref);
END;
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
SQL-00: Establish and follow clear rules for how to write SQL in your application.
Content preview·Buy reprint rights for this chapter
SQL-00: Establish and follow clear rules for how to write SQL in your application.
  • Never repeat a SQL statement.
  • Encapsulate all SQL statements behind a procedural interface (usually a package).
  • Write your code assuming that the underlying data structures will change.
  • Take advantage of PL/SQL-specific enhancements for SQL.
All these topics—with examples, benefits, and challenges—are explored in the more detailed best practices in this chapter.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
General SQL and Transaction Management
Content preview·Buy reprint rights for this chapter
This section contains some general-purpose best practices for writing SQL statements and some specific best practices for handling transactions.
SQL-01: Qualify PL/SQL variables with their scope names when referenced inside SQL statements.
You could declare a variable that has the same name as a table, a column, or a view. The PL/SQL compiler won't get confused, but you might, and your SQL statements inside PL/SQL might not work as intended. So you should always make sure that there is no ambiguity between SQL and PL/SQL identifiers. The best way to do this is to qualify all references to PL/SQL variables with their scope name.
Example
Consider the following block:
CREATE OR REPLACE PROCEDURE show_fav_flavor (
   pref_type IN VARCHAR2)
IS
   pref VARCHAR2(100);
BEGIN
   SELECT preference INTO pref
     FROM personal_preferences PP
    WHERE PP.pref_type = pref_type;
   pl (pref);
END;
You might think that the WHERE clause restricts the query to only those rows where pref_type equals the value passed in through the parameter. In fact, it's no different logically than "1 = 1". SQL always takes precedence over PL/SQL when resolving identifiers.
There are two solutions to this problem:
  • Use prefixes/suffixes on variable and parameter names to distinguish them from column and table names, as in: 
    CREATE OR REPLACE PROCEDURE show_fav_flavor (
   pref_type_in IN VARCHAR2)
  • Always qualify references to PL/SQL elements inside the SQL statement, as in: 
    SELECT preference INTO pref
  FROM personal_preferences PP
 WHERE PP.pref_type = show_fav_flavor.pref_type;
I recommend the second approach. It requires more typing, but it's foolproof. With the first solution, for example, a DBA can conceivably add a column to the personal_preferences table called pref_type_in and completely muck up my code!
Benefits
The behavior of your SQL statements will be predictable and consistent over time, regardless of changes to the underlying data structures.
Challenges
You have to write more code, qualifying all references to those variables inside SQL.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Querying Data from PL/SQL
Content preview·Buy reprint rights for this chapter
The best practices in this section apply when you are querying data from PL/SQL programs.
SQL-04: Put single-row fetches inside functions; never hard-code a query in your block.
Always put your single-row query inside a function, and then call the function to return the information you need (whether it's a scalar value, an entire record, or even a collection) through the RETURN clause.
Example
Instead of writing code like this:
BEGIN
   SELECT title INTO l_title   -- HARD-CODED
     FROM book                 -- QUERY...
    WHERE isbn =isbn_in;       -- BAD IDEA!
you should create a function, ideally within a "table encapsulation package": 
PACKAGE te_book
IS
   FUNCTION title (isbn_in IN book.isbn%TYPE)
      RETURN book.title%TYPE;
Now your application code looks like this:
BEGIN
   l_title := te_book.title (isbn_in);
Benefits
Optimal performance: The query is written once, presumably by the developer who best knows how to write it. Since there is a single physical representation of the query in code, the parsed version of the cursor is cached and used repeatedly.
Easy maintenance: If you need to change the query, you only have to do it in one place.
Consistent error handling: Individual developers don't have to remember to write handlers for NO_DATA_FOUND and TOO_MANY_ROWS.
Challenges
Discipline is required in a multi-person team environment to ensure that the team has at least one person overseeing this type of encapsulation and that the whole team adheres to this standard.
There will be a larger volume of code to write and manage (your DBA must size the System Global Area accordingly). Explore the possibilities of generating these functions from the data dictionary.
SQL-05: Hide reliance on the dual table.
This is a special case of [SQL-04: Put single-row fetches inside functions; never hard-code a query in your block.] but is worth mentioning. The dual table is a "dummy" table that is used by Oracle itself and by many developers to access functionality in the SQL engine that is otherwise not available in PL/SQL.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Changing Data from PL/SQL
Content preview·Buy reprint rights for this chapter
With PL/SQL, you can not only query information from an underlying Oracle database but also change data in tables with the INSERT, UPDATE, and DELETE operations.
SQL-15: Encapsulate INSERT, UPDATE, and DELETE statements behind procedure calls.
Write a standalone procedure or put such procedures inside a single "table encapsulation package," but never, ever embed DML statements directly within application code.
Take the "Know Thy SQL" test: pick a table, any critical table in your application schema. Ask yourself this question: "Do I know where all or any of the INSERT statements for this table appear in my code?" Chances are that you can't answer definitively, and that is because we PL/SQL developers are somewhat haphazard about managing our SQL statements. The result? Tremendous obstacles to performing accurate impact analysis on your code from database changes, among other things.
Example
Instead of writing an INSERT as follows:
INSERT INTO book (
   isbn, title, author)
VALUES (
   '1-56592-675-7',
   'Oracle PL/SQL Programming Guide to Oracle8i Features',
   'Feuerstein, Steven');
use a standalone procedure, as in: 
add_book (
   '1-56592-675-7',
   'Oracle PL/SQL Programming Guide to Oracle8i Features',
   'Feuerstein, Steven');
or a packaged procedure:
te_book.ins (
   '1-56592-675-7',
   'Oracle PL/SQL Programming Guide to Oracle8i Features',
   'Feuerstein, Steven');
Benefits
Your application runs faster. All programs that perform inserts into a given table use exactly the same INSERT, which results in less parsing and reduced demands on SGA memory.
Your application handles DML-related errors consistently. It's not up to individual developers to write error-logging mechanisms or decide how to deal with particular errors.
Challenges
You need to write or generate more procedural code.
Your DBA may need to adjust the size of the shared pool area to handle the increased volume of code.
You may need to create multiple update procedures, to match up with various combinations of columns that you update in your application.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Dynamic SQL and Dynamic PL/SQL
Content preview·Buy reprint rights for this chapter
"Dynamic" means that the SQL statement or PL/SQL block that you execute is constructed, parsed, and compiled at runtime, not at the time the code is compiled. Dynamic SQL offers a tremendous amount of flexibility—but also complexity.
With Oracle8i and above, you can use native dynamic SQL (NDS) to take care of dynamic SQL. Prior to Oracle8i, you must rely on the DBMS_SQL built-in package.
SQL-19: Encapsulate dynamic SQL parsing to improve error detection and cleanup.
Dynamic SQL is tricky; you generally glue together different chunks of text (with the concatenation operator) to form what you hope is a valid SQL or PL/SQL statement. Either through programmer error or user error, you can end up with a bad chunk of SQL, resulting in a parse error.
To identify and fix these errors, you should create your own parsing "engine" on top of DBMS_SQL.PARSE and the NDS statements. This program traps and displays error information, and cleans up any cursors.
Example
This technique is most crucial for DBMS_SQL. Don't ever call DBMS_SQL.PARSE directly in your program. Instead, call your own parse encapsulator. Why would you bother to do this? Consider the following block of code. It leaves a DBMS_SQL cursor unclosed and unclosable; you need to be able to reference the dyncur variable in the call to DBMS_SQL.CLOSE_CURSOR, but that variable is erased once the exception is propagated: 
DECLARE
    dyncur PLS_INTEGER := DBMS_SQL.open_cursor;
BEGIN
    -- Whoops, forget the FROM clause!
    DBMS_SQL.parse (
       dyncur, 'select * dual', DBMS_SQL.native);
END;
Here's a very simple example of an encapsulation for DBMS_SQL.PARSE: 
CREATE OR REPLACE FUNCTION open_and_parse ( 
   dynsql_in IN VARCHAR2, 
   dbms_mode_in IN INTEGER := NULL) 
RETURN INTEGER
IS
   dyncur INTEGER;
BEGIN
   dyncur := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE (dyncur, dynsql_in, 
      NVL (dbms_mode_in, DBMS_SQL.NATIVE));

   RETURN dyncur;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_SQL.CLOSE_CURSOR (dyncur);
      pl (SQLERRM);
      pl (dynsql_in);
      RETURN NULL;
END;
/
See Section for a more comprehensive solution with DBMS_SQL.
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Chapter 7: Program Construction
Content preview·Buy reprint rights for this chapter
There are three kinds of programs (also known as modules) in PL/SQL:
Procedure
A procedure is a program that executes one or more statements. It's called as a standalone statement.
Function
A function is a program that executes one or more statements and returns a value. It's called within an expression (assignment statement, conditional expression, etc.).
Trigger
A trigger is a program whose execution is "triggered" by some event, usually a SQL operation on a table or column within a table.
All of these are named, executable code units. A package, as described in Chapter 8, is a container for procedures and/or functions, as well as data. Packages, therefore, aren't executable objects themselves.
The best practices in this section offer advice on how to structure your program units and how best to design parameter lists.
MOD-01: Encapsulate and name business rules and formulas behind function headers.
This is one of the most important best practices you will ever read—and, I hope, follow. The one aspect of any software project that never changes is that stuff always changes. Business requirements, data structures, user interfaces: all these things change and change frequently. Your job as a programmer is to write code that adapts easily to these changes.
So whenever you need to express a business rule (such as, "Is this string a valid ISBN?"), put it inside a subroutine that hides the individual steps (which might change) and returns the results (if any).
And whenever you need a formula (such as, "the total fine for an overdue book is the number of days overdue times $.50"), express that formula inside its own function.
Example
Suppose that you must be at least 10 years old to borrow books from the library. This is a simple formula and very unlikely to change. I set about building the application by creating the following trigger: 
CREATE OR REPLACE TRIGGER are_you_too_young
   AFTER insert OR update 
   ON borrower FOR EACH ROW
BEGIN
   IF :new.date_of_birth > 
         ADD_MONTHS (SYSDATE, -12 * 10)
   THEN
      RAISE_APPLICATION_ERROR (
         -20703, 
        'Borrower must be at least 10 yrs old.');
   END IF;
END;
/
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Structure and Parameters
Content preview·Buy reprint rights for this chapter
The best practices in this section offer advice on how to structure your program units and how best to design parameter lists.
MOD-01: Encapsulate and name business rules and formulas behind function headers.
This is one of the most important best practices you will ever read—and, I hope, follow. The one aspect of any software project that never changes is that stuff always changes. Business requirements, data structures, user interfaces: all these things change and change frequently. Your job as a programmer is to write code that adapts easily to these changes.
So whenever you need to express a business rule (such as, "Is this string a valid ISBN?"), put it inside a subroutine that hides the individual steps (which might change) and returns the results (if any).
And whenever you need a formula (such as, "the total fine for an overdue book is the number of days overdue times $.50"), express that formula inside its own function.
Example
Suppose that you must be at least 10 years old to borrow books from the library. This is a simple formula and very unlikely to change. I set about building the application by creating the following trigger: 
CREATE OR REPLACE TRIGGER are_you_too_young
   AFTER insert OR update 
   ON borrower FOR EACH ROW
BEGIN
   IF :new.date_of_birth > 
         ADD_MONTHS (SYSDATE, -12 * 10)
   THEN
      RAISE_APPLICATION_ERROR (
         -20703, 
        'Borrower must be at least 10 yrs old.');
   END IF;
END;
/
Later, while building a batch-processing script that checks and loads over 10,000 borrower applications, I include the following check in the program: 
BEGIN
   ...
   IF ADD_MONTHS (SYSDATE, -122) > rec.date_of_birth 
   THEN
      err.log ('Borrower ' || rec.borrower_id ||
         ' is not ten years old.');
   ELSE
      ...load the data
And so on from there. I am left, unfortunately, with a real job on my hands when I get a memo that says: "The minimum age for a library card has been changed from 10 to 8 in order to support a new city-wide initiative to increase literacy." And then, of course, there are also the two bugs I introduced into my second construction of the rule. Did you notice them and the inconsistent error messages? The IF statement should read:
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Functions
Content preview·Buy reprint rights for this chapter
Functions are program units that return a value through the RETURN clause of the program header.
MOD-07: Limit functions to a single RETURN statement in the execution section.
A good general rule to follow as you write your PL/SQL programs is: "one way in and one way out." In other words, there should be just one way to enter or call a program (there is; you don't have any choice in this matter). And there should be one way out, one exit path from a program (or loop) on successful termination. By following this rule, you end up with code that is much easier to trace, debug, and maintain.
For a function, this means you should think of the executable section as a funnel; all the lines of code narrow down to the last executable statement: 
RETURN return value;
Note the following:
  • You can, and should, still have RETURN statements in your exception handlers. Not every exception should be passed unhandled from your function. See [EXC-07: Handle exceptions that cannot be avoided but can be anticipated.] for more information.
  • It's possible (i.e., acceptable syntax) to use an "unqualified" RETURN statement in a procedure, as follows: 
    IF all_done
THEN
   RETURN;
END IF;
    and the procedure immediately terminates and returns control. You shouldn't do this, however, as it results in unstructured code that's hard to debug and maintain This same recommendation holds for the initialization section of a package.
Example
Here's a simple function that relies on multiple RETURNs:
CREATE OR REPLACE FUNCTION status_desc (
   cd_in IN VARCHAR2
)
   RETURN VARCHAR2
IS
BEGIN
   IF cd_in = 'C'
   THEN
      RETURN 'CLOSED';
   ELSIF cd_in = 'O'
   THEN
      RETURN 'OPEN';
   ELSIF cd_in = 'I'
   THEN
      RETURN 'INACTIVE';
   END IF;
END;
At first glance, this function looks very reasonable. Yet this function has a deep flaw, due to the reliance on separate RETURNs: if you don't pass in "C", "O", or "I" for the cd_in argument, the function raises: 
ORA-06503: PL/SQL: Function returned without value
Here's a rewrite that relies on (a) a standard types package that avoids hard-coding a VARCHAR2 variable length (see [DAT-13: Centralize TYPE definitions in package specifications.]) and also gives names to literal values, and (b) a single RETURN at the end of the function:
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Triggers
Content preview·Buy reprint rights for this chapter
Database triggers are a crucial part of any well-designed application. By placing logic in a trigger, you associate business rules closely with the database object, guaranteeing that these rules are always applied to any action taken against the object.
MOD-10: Minimize the size of trigger execution sections.
Limit the number of lines of code in a trigger—even to the point of moving code into procedures, functions, or packages and calling them from the trigger.
Prior to Oracle7 Release 7.3, trigger code wasn't even stored in the database in compiled form. Each time a trigger was executed, it would also have to be compiled. Under these conditions, it was absolutely critical to move as much trigger code as possible to stored, precompiled procedures in order to improve the trigger's execution time.
Now, triggers are compiled just as procedures and functions are. Still, you should move as much of your business logic as possible to packaged programs.
Example
Well, I could offer an example of pages and pages of code that are replaced by a single procedure call. That would certainly drive the point home—but at the expense of a few more trees. So instead, here is a very small trigger, but one that still exposes a business rule that should be hidden: 
CREATE OR REPLACE TRIGGER check_employee_age
BEFORE INSERT OR UPDATE ON employee
BEGIN
   IF ADD_MONTHS (SYSDATE, -216) < :NEW.hire_date
   THEN
      RAISE_APPLICATION_ERROR (-20706, 
         'Employee must be 18 to work here!');
   END IF;
END;
A much improved implementation would be:
CREATE OR REPLACE TRIGGER check_employee_age
BEFORE INSERT OR UPDATE ON employee
BEGIN
   IF employee_rules.emp_too_young (:NEW.hire_date)
   THEN
      err_pkg.rase (employee_rules.c_errnum_emp_too_young,
         :NEW.employee_id);
   END IF;
END;
Now that business rule (which in the "real world" might have been very complex and taken up several lines of code) is moved to the package. I have also avoided the hard-coding of a RAISE_APPLICATION_ERROR call relying on my standard error package (see [EXC-04: Use your own raise procedure in place of explicit calls to RAISE_APPLICATION_ERROR.]).
Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!
Chapter 8: Package Construction
Content preview·Buy reprint rights for this chapter
Packages are the fundamental building blocks of any well-designed application built in the Oracle PL/SQL language (at least until Oracle improves the robustness of its object implementation!). A package consists of up to two elements: the specification and the body. The specification tells a user what she can do with the package: what programs can be called, what data structures and user-defined types can be referenced, and so on. The package body implements any programs in the package specification; it can also contain private (i.e., not shown in the specification) data structures and program units.
PKG-01: Group related data structures and functionality together in a single package.
Even if you don't take advantage of features unique to packages, such as persistent data, you can still use packages to organize—and store together—related code and data structures.
Without packages, you might end up with several hundred standalone procedures and functions and many repeated cursor, TYPE, and variable declarations.
A package gives a name to a set of program elements: procedures, functions, user-defined types, variable and constant declarations, cursors, and so on. By creating a package for each distinct area