Oracle PL/SQL Best Practices

Name

DAT-06: Perform complex variable initialization in the executable section.

Synopsis

The exception section of a block can trap only errors raised in the executable section of that block. So if the code you run to assign a default value to a variable fails in the declaration section, that error is propagated unhandled out to the enclosing program. It’s difficult to debug these problems, so, you must either:

• Be sure that initialization logic doesn’t raise an error.

• Perform your initialization at the beginning of the executable section, preferably in a separate “init” program.

Example

Here’s some dangerous code, since it isn’t at all apparent what these functions do and what they pass back:

CREATE OR REPLACE PROCEDURE find_bestsellers
IS
   l_last_title book.title%TYPE :=
      last_search (SYSDATE);
   
   l_min_count INTEGER(3) := 
      bestseller.limits (bestseller.low);
BEGIN

And here is a much safer approach:

CREATE OR REPLACE PROCEDURE find_bestsellers
IS
   l_last_title book.title%TYPE;   
   l_min_count INTEGER(3);

   PROCEDURE init IS
   BEGIN
      l_last_title:= last_search (SYSDATE);
      l_min_count:= 
         bestseller.limits (bestseller.low);
   EXCEPTION
       -- Trap and handle all errors 
       -- inside the program
   END;
BEGIN
   init;

Benefits

Your programs will behave more reliably; if an error does occur as you initialize variables, you can trap the error locally and decide how you want to handle the situation.