Conditional Compilation for Oracle Database 10g - Examples of Conditional Compilation
(Page 2 of 4 )
Let’s start with some examples of several types of conditional compilation.
Use application package constants in $IF directive
The $IF directive can reference constants defined in your own packages. In the example below, I vary the way that the bonus is applied depending on whether or not the location in which this third-party application is installed is complying with the Sarbanes-Oxley guidelines. Such a setting is unlikely to change for a long period of time. If I rely on the traditional conditional statement in this case, I will leave in place a branch of logic that should never be applied. With conditional compilation, the code is removed before compilation.
/* File on web: cc_my_package.sql */
CREATE OR REPLACE PROCEDURE apply_bonus (
id_in IN employee.employee_id%TYPE
,bonus_in IN employee.bonus%TYPE)
IS
BEGIN
UPDATE employee
SET bonus =
$IF employee_rp.apply_sarbanes_oxley
$THEN
LEAST (bonus_in, 10000)
$ELSE
bonus_in
$END
WHERE employee_id = id_in;
NULL;
END apply_bonus;
/
Toggle tracing through conditional compilation flags
We can now set up our own debug/trace mechanisms and have them conditionally compiled into our code. This means that when our code rolls into production, we can have this code completely removed, so that there will be no runtime overhead to this logic. Note that I can specify both Boolean and PLS_INTEGER values through the special PLSQL_CCFLAGS compile parameter.
/* File on web: cc_debug_trace.sql */
ALTER SESSION SET PLSQL_CCFLAGS = 'oe_debug:true, oe_trace_level:10';
CREATE OR REPLACE PROCEDURE calculate_totals
IS
BEGIN
$IF $$oe_debug AND $$oe_trace_level >= 5
$THEN
DBMS_OUTPUT.PUT_LINE ('Tracing at level 5 or higher');
$END
NULL;
END calculate_totals;
/
Next: The Inquiry Directive >>
More Oracle Articles
More By O'Reilly Media
|
 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). Buy this book now.
|
|
