Home arrow Oracle arrow Page 2 - Working With PL/SQL Code

Synchronizing Code with Packaged Constants - Oracle

In this sixth part of a nine-part series on managing PL/SQL code, you will learn how to synchronize code with packaged constants, how to work with post-processed code, and more. 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). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

TABLE OF CONTENTS:
  1. Working With PL/SQL Code
  2. Synchronizing Code with Packaged Constants
  3. Working with Postprocessed Code
  4. Testing PL/SQL Programs
By: O'Reilly Media
Rating: starstarstarstarstar / 9
November 29, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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
  
IS
  
BEGIN
  
4     NULL;
  
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



 
 
>>> More Oracle Articles          >>> More By O'Reilly Media
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: