Working With PL/SQL Code - Synchronizing Code with Packaged Constants

(Page 2 of 4 )

Use of packaged constants within a selection directive allows you to easily synchronize multiple program units around a specific conditional compilation setting. This is possible because Oracle’s automatic dependency management is applied to selection directives. In other words, if program unit PROG contains a selection directive that references package PKG, then PROG is marked as dependent on PKG. When the specification of PKG is recompiled, all program units using the packaged constant are marked invalid and must be recompiled.

Suppose I want to use conditional compilation to automatically include or exclude debugging and tracing logic in my code base. I define a package specification to hold the required constants:

  /* File on web: cc_debug.pks */
  CREATE OR REPLACE PACKAGE cc_debug
  IS

     debug_active CONSTANT BOOLEAN := TRUE;
     trace_level CONSTANT PLS_INTEGER := 10;
  END cc_debug;
  /

I then use these constants in procedure calc_totals:

  CREATE OR REPLACE PROCEDURE calc_totals
  IS
  BEGIN
  $IF cc_debug.debug_active AND cc_debug.trace_level > 5 $THEN
     log_info (...);
  $END
     ...
  END calc_totals;
  /

During development, the debug_active constant is initialized to TRUE. When it is time to move the code to production, I change the flag to FALSE and recompile the package. The calc_totals program and all other programs with similar selection directives are marked invalid and must then be recompiled.

Program-Specific Settings with Inquiry Directives

Packaged constants are useful for coordinating settings across multiple program units. Inquiry directives, drawn from the compilation settings of individual programs, are a better fit when you need different settings applied to different programs.

Once you have compiled a program with a particular set of values, it will retain those values until the next compilation (either from a file or a simple recompilation using the ALTER...COMPILE statement). Furthermore, a program is guaranteed to be recompiled with the same postprocessed source as was selected at the time of the previous compilation if all of the following conditions are TRUE:

1. None of the conditional compilation directives refer to package constants. Instead, they rely only on inquiry directives.
   
2. When the program is recompiled, the REUSE SETTINGS clause is used and the PLSQL_CCFLAGS parameter isn’t included in the ALTER...COMPILE command.

This capability is demonstrated by the cc_reuse_settings.sql script, whose output is shown below. I first set the value of app_debug to TRUE and then compile a program with that setting, A query against USER_PLSQL_OBJECT_SETTINGS shows that this value is now associated with the program unit:

  /* File on web: cc_reuse_settings.sql */

  SQL> ALTER SESSION SET plsql_ccflags = 'app_debug:TRUE';

  SQL> CREATE OR REPLACE PROCEDURE test_ccflags
    2  IS
    3  BEGIN
    4     NULL;
    5  END test_ccflags;
    6  /

  SQL> SELECT name, plsql_ccflags
    2    FROM user_plsql_object_settings
    3  WHERE NAME LIKE '%CCFLAGS%';

  NAME               PLSQL_CCFLAGS
  ------------------ -----------------------
  TEST_CCFLAGS       app_debug:TRUE

I now alter the session, setting $$app_debug to evaluate to FALSE. I compile a new program with this setting:

  SQL> ALTER SESSION SET plsql_ccflags = 'app_debug:FALSE';

  SQL> CREATE OR REPLACE PROCEDURE test_ccflags_new
   2  IS
   3  BEGIN
   4     NULL;
   5  END test_ccflags_new;
   6  /

Then I recompile my existing program with REUSE SETTINGS:

 SQL> ALTER PROCEDURE test_ccflags COMPILE REUSE SETTINGS;

A query against the data dictionary view now reveals that my settings are different for each program:

  SQL> SELECT name, plsql_ccflags
   2     FROM user_plsql_object_settings
   3   WHERE NAME LIKE '%CCFLAGS%';

  NAME                 PLSQL_CCFLAGS
  -------------------- ---------------------
  TEST_CCFLAGS         app_debug:TRUE 
  TEST_CCFLAGS_NEW     app_debug:FALSE

Next: Working with Postprocessed Code >>
 

More Oracle Articles
More By O'Reilly Media

Comments On: Working With PL/SQL Code

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

Buy this book...

This article is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O'Reilly; ISBN: 0596009771). Check it out today at your favorite bookstore. Buy this book now.