SunQuest
 
       Database Articles
  Home arrow Database Articles arrow Page 3 - What`s Code Without Variables?
Codewalker Forums 
  Tutorials  
Database Articles  
Miscellaneous  
Navigation Usability  
PEAR Articles  
Programming Basics  
Server Administration  
XML Tutorials  
  Reviews  
Database Book Reviews  
Linux Book Reviews  
Miscellaneous Reviews  
PHP Book Reviews  
PHP Software Reviews  
Server Admin Reviews  
SQL Tool Reviews  
  Code Gallery  
Content Management Code  
Contest Code  
Counters Code  
Database Code  
Date Time Code  
Discussion Board Code  
Email Code  
File Manipulation Code  
GUI Code  
Link Farm Code  
Miscellaneous Code  
Search Code  
Site Navigation Code  
User Management Code  
Forums Sitemap 
Dedicated Servers  
Download TestComplete 
IBM® developerWorks
Weekly Newsletter 
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
DATABASE ARTICLES

What`s Code Without Variables?
By: O'Reilly Media
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 3 stars3 stars3 stars3 stars3 stars / 2
    2007-12-13

    Table of Contents:
  • What`s Code Without Variables?
  • There is more to data than columns in a table
  • I take exception to your declaration section
  • Best Practices for Using Variables and Data Structures

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT

    Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here

    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;
        
    /
     
    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.

    More Database Articles Articles
    More By O'Reilly Media


       · This article is an excerpt from the book "Oracle PL/SQL Best Practices, Second...
     

    Buy this book now. 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.

    DATABASE ARTICLES ARTICLES

    - More on Query Optimization for Oracle Databa...
    - Query Optimization in Oracle
    - Clusters and Other Data Structures for Oracle
    - Using Indexes with an Oracle Database
    - The Basics of Data Structures in Oracle
    - Oracle Data Structures
    - Best Practices for PL/SQL Variables
    - What`s Code Without Variables?
    - Clauses, Sorting, and SQL Queries
    - The From Clause and SQL Queries
    - Query Primer
    - Full Text Searches and Strings
    - Searching with Strings
    - Pattern Matching with Strings
    - Working with Cases of Strings






    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 5 hosted by Hostway