What`s Code Without Variables? - I take exception to your declaration section

(Page 3 of 4 )

Perform complex variable initialization in the execution section.

Problem: The exception section of a block can only trap errors raised in the execution section.

That is a little fact that many PL/SQL developers don’t realize, and one that causes lots of headaches. Delaware writes the following packaged function, a classic “getter” of a private variable:

  PACKAGE fe_config
  IS
     FUNCTION get_worst_excuse RETURN VARCHAR2;
  END fe_config;
  /

  PACKAGE BODY fe_config
  IS
     c_worst_excuse CONSTANT VARCHAR2 (20) :=
        'The dog ate my homework. Really.';

     FUNCTION get_worst_excuse RETURN VARCHAR2
     IS
     BEGIN
        RETURN c_worst_excuse;
     END get_worst_excuse;
  BEGIN
     DBMS_OUTPUT.put_line ('Initialization logic here');
     ... lots of initialization code ...
  EXCEPTION
     WHEN OTHERS
     THEN
        fe_errmgr.log_and_raise_error;
  END fe_config;
  /

As far as Delaware can tell, he has set things up so that if anything goes wrong while initializing the package, he will trap and log the error. Yet when he tries to call the function, he gets an unhandled exception:

  SQL> BEGIN
    2     DBMS_OUTPUT.PUT_LINE (fe_config.get_worst_excuse ());
    3  END;
    4  /
  BEGIN
  *
  ERROR at line 1:
  ORA-06502: PL/SQL: numeric or value error: character string buffer too small
  ORA-06512: at "HR.FE_CONFIG", line 3

For a solid five minutes, Delaware stares at this simple package, stumped. Then he groans and smacks his forehead. Of course! The exception section that is underneath the package initialization section will trap only exceptions that occur in that initialization section (the execution section of a package). And 20
characters simply aren’t enough for the world’s worst excuse.

Now Delaware could simply raise the length of that constant’s VARCHAR2 declaration. But he would rather fix the problem in a more long-lasting and fundamental way.

Solution: Don’t trust the declaration section to assign default values.

As we’ve seen, the exception section of a block can trap only errors raised in the execution 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, too, so you must either:

1. Be sure that your initialization logic doesn’t ever raise an error. That’s hard to guarantee, isn’t it?
   
2. Perform your initialization at the beginning of the execution section, preferably in a separate “initialization” program.

Here’s what Delaware did with his package:

1. He did no more hardcoding of the VARCHAR2 length. He anchored to a database column instead.
   
2. He moved the assignment of the default value into a separate procedure, and called this procedure in the package’s initialization section.

It is particularly important to avoid assigning default values in the declaration section if they are function calls or expressions that make it hard to predict the value that will be returned.

Here is Delaware’s new code:

  PACKAGE BODY fe_config
  IS
     g_worst_excuse flimsy_excuse.title%TYPE;

     FUNCTION get_worst_excus RETURN VARCHAR2 IS
     BEGIN
        RETURN g_worst_excuse;
     END get_worst_excuse;

     PROCEDURE initialize IS
     BEGIN
        g_worst_excuse := 'The dog ate my homework. Really.';
     END initialize;
  BEGIN
     initialize;
  EXCEPTION
     WHEN OTHERS
     THEN
        fe_errmgr.log_and_raise_error;
  END fe_config;

Now if that string is too long, the exception section will catch the problem and the error logging will come into play.

Next: Best Practices for Using Variables and Data Structures >>
 

More Database Articles Articles
More By O'Reilly Media

Comments On: What`s Code Without Variables?

· This article is an excerpt from the book "Oracle PL/SQL Best Practices, Second...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter four of the book Oracle PL/SQL Best Practices, Second Edition, written by Steven Feuerstein (O'Reilly, 2007; ISBN: 0596514107). Check it out today at your favorite bookstore. Buy this book now.