Examples of Conditional Compilation

View All Scripts Login to Run Script

Script Name Examples of Conditional Compilation
Description Use "$" syntax to affect which lines of code in your source program will be compiled and which ignored. Conditional compilation feature can be of great assistance when writing code to run on multiple versions of Oracle and can be used to enforce at compile time code quality rules, among many applications of this feature. Best resource on conditional compilation: http://www.oracle.com/technetwork/database/features/plsql/overview/plsql-conditional-compilation-133587.pdf
Area PL/SQL General
Contributor Steven Feuerstein
Created Wednesday December 02, 2015

Statement 1

Set Up Table

CREATE TABLE employees AS SELECT * FROM hr.employees

Table created.

Statement 2

Package to Support Bulk Insert

CREATE OR REPLACE PACKAGE employees_dml 
IS  
   TYPE demo_aat IS TABLE OF employees%ROWTYPE  
      INDEX BY PLS_INTEGER;  
  
   PROCEDURE insert_rows ( rows_in IN demo_aat );  
END;

Package created.

Statement 3

Prior to 10.2, you could not use a sparse collection (at least one element not defined between FIRST and LAST) as a bind array in FORALL. With 10.2, INDICES OF (and VALUES OF) were introduced. This use of conditional compilation shows that I can keep the "old code" (gets rid of gaps) in place for older installations of my application, but switch to INDICES OF for newer database instances.

Use DBMS_DB_VERSION to Optimize Code

CREATE OR REPLACE PACKAGE BODY employees_dml  
IS  
   PROCEDURE insert_rows ( rows_in IN demo_aat )  
   IS  
   BEGIN  
$IF DBMS_DB_VERSION.VER_LE_10_1  
$THEN  
   DECLARE  
      l_dense demo_aat;  
      l_index PLS_INTEGER := rows_in.FIRST;  
   BEGIN  
      WHILE (l_index IS NOT NULL)  
      LOOP  
         l_dense (l_dense.COUNT + 1) := rows_in (l_index);  
         l_index := rows_in.NEXT (l_index);  
      END LOOP;  
        
      FORALL indx IN l_dense.FIRST .. l_dense.LAST  
         INSERT INTO employees VALUES l_dense (indx);  
   END;  
$ELSE  
      FORALL indx IN INDICES OF rows_in  
         INSERT INTO employees VALUES rows_in (indx);  
$END  
   END insert_rows;  
END;

Package Body created.

Statement 4

I want to ensure that this procedure is ALWAYS compiled with optimization level at least 2. This code will cause a compilation failure if the optimization level is set to 0 or 1.

Quality Assurance with Conditional Compilation

CREATE OR REPLACE PROCEDURE compute_intensive 
AUTHID DEFINER 
IS 
BEGIN 
   $IF $$plsql_optimize_level < 2 
   $THEN 
      $ERROR 'compute_intensive must be compiled with maximum optimization!' $END 
   $END 
   NULL; 
END compute_intensive;

Procedure created.

Statement 5
A commonly-expressed frustration of hiding subprograms in package bodies is that they cannot be independently unit-tested. You can use conditional compilation to "expose" (declare in the package specification) private subprograms during test phase, and then make sure they are hidden for production use.
Hide/Expose Private Subprograms for Testing
```
ALTER SESSION SET PLSQL_CCFLAGS = 'show_private_joke_programs:FALSE' 
```
```
Statement processed.
```

Statement 6

A commonly-expressed frustration of hiding subprograms in package bodies is that they cannot be independently unit-tested. You can use conditional compilation to "expose" (declare in the package specification) private subprograms during test phase, and then make sure they are hidden for production use.

Hide/Expose Private Subprograms for Testing

CREATE OR REPLACE PACKAGE sense_of_humor 
IS 
   PROCEDURE calc_how_funny ( 
      joke_in               IN       VARCHAR2 
    , funny_rating_out      OUT      NUMBER 
    , appropriate_age_out   OUT      NUMBER 
   ); 
$IF $$show_private_joke_programs $THEN 
   FUNCTION humor_level ( joke_in IN VARCHAR2 ) 
      RETURN NUMBER; 
             
    FUNCTION maturity_level ( joke_in IN VARCHAR2 ) 
      RETURN NUMBER; 
$END          
END;

Package created.

Statement 7

Hide/Expose Private Subprograms for Testing

CREATE OR REPLACE PACKAGE BODY sense_of_humor 
IS 
   FUNCTION humor_level ( joke_in IN VARCHAR2 ) 
      RETURN NUMBER 
   IS 
   BEGIN 
      -- Some really interesting code here... 
      RETURN 100; 
   END humor_level; 
 
   FUNCTION maturity_level ( joke_in IN VARCHAR2 ) 
      RETURN NUMBER 
   IS 
   BEGIN 
      -- Some really interesting code here... 
      RETURN 100; 
   END maturity_level; 
 
   PROCEDURE calc_how_funny ( 
      joke_in               IN       VARCHAR2 
    , funny_rating_out      OUT      NUMBER 
    , appropriate_age_out   OUT      NUMBER 
   ) 
   IS 
   BEGIN 
      funny_rating_out := humor_level ( joke_in ); 
      appropriate_age_out := maturity_level ( joke_in ); 
   END calc_how_funny; 
END;

Package Body created.

Statement 8
As you would expect: since the conditional compilation flag was set to FALSE, those headers are not included in the specification.
Private Subprogram Cannot Be Executed
```
BEGIN 
   DBMS_OUTPUT.PUT_LINE (sense_of_humor.humor_level ( 
      'Why did the chicken cross the road?')); 
END; 
```
```
ORA-06550: line 2, column 41:
PLS-00302: component 'HUMOR_LEVEL' must be declared 
```
More Details: https://docs.oracle.com/error-help/db/ora-06550
Statement 9
Now I tell the compiler that I want to include those function headers in the specification.
Change CC Flag and Recompile
```
ALTER SESSION SET PLSQL_CCFLAGS = 'show_private_joke_programs:TRUE' 
```
```
Statement processed.
```
Statement 10
Now I tell the compiler that I want to include those function headers in the specification.
Change CC Flag and Recompile
```
ALTER PACKAGE sense_of_humor COMPILE 
```
```
Package altered.
```

Statement 11

BEGIN 
   DBMS_OUTPUT.PUT_LINE (sense_of_humor.humor_level ( 
      'Why did the chicken cross the road?')); 
END;

Statement 12

Oracle provides a number of pre-defined CC flags - mostly reflecting the contents of the all_plsql_object_settings view.

Helper Procedure Showing CC Flag Values

CREATE OR REPLACE PROCEDURE show_cc_parameters 
IS 
   /* Reflect contents of all_plsql_object_settings */ 
BEGIN 
   IF $$plsql_debug 
   THEN 
      DBMS_OUTPUT.put_line ('DEBUG ON'); 
   ELSE 
      DBMS_OUTPUT.put_line ('DEBUG OFF'); 
   END IF; 
 
   DBMS_OUTPUT.put_line ('Opt Level = ' || $$plsql_optimize_level); 
   DBMS_OUTPUT.put_line ('Code Type = ' || $$plsql_code_type); 
   DBMS_OUTPUT.put_line ('Warnings Setting = ' || $$plsql_warnings); 
   DBMS_OUTPUT.put_line ('NLS Length Semantics = ' || $$nls_length_semantics); 
   DBMS_OUTPUT.put_line ('CC Flags = ' || $$plsql_ccflags); 
   DBMS_OUTPUT.put_line ('PLScope = ' || $$plscope_settings); 
END show_cc_parameters;

Procedure created.

Statement 13

ALTER SESSION SET plsql_warnings = 'ENABLE:ALL'

Statement processed.

Statement 14

ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL'

Statement processed.

Statement 15

BEGIN 
   show_cc_parameters (); 
END;

DEBUG OFF
Opt Level = 2
Code Type = INTERPRETED
Warnings Setting = DISABLE:ALL
NLS Length Semantics = BYTE
CC Flags = show_private_joke_programs:TRUE
PLScope = IDENTIFIERS:NONE

Statement 16
Demonstration of $IF to control level and amount of tracing
Use $IF to Control Tracing
```
ALTER SESSION SET PLSQL_CCFLAGS =  'oe_debug:true, oe_trace_level:10' 
```
```
Statement processed.
```

Statement 17

Demonstration of $IF to control level and amount of tracing

Use $IF to Control Tracing

CREATE OR REPLACE PROCEDURE calculate_totals 
IS 
BEGIN 
$IF $$oe_debug AND $$oe_trace_level >= 5 
$THEN 
   -- q$error_manager.trace ... 
   DBMS_OUTPUT.PUT_LINE ('Tracing at level 5 or higher'); 
$END             
   DBMS_OUTPUT.PUT_LINE ('Normal processing'); 
END calculate_totals;

Warning: PROCEDURE CALCULATE_TOTALS
Line/Col: 1/1 PLW-05018: unit CALCULATE_TOTALS omitted optional AUTHID clause; default value DEFINER used

More Details: https://docs.oracle.com/error-help/db/ora-20001

Statement 18
Demonstration of $IF to control level and amount of tracing
Use $IF to Control Tracing
```
BEGIN 
   calculate_totals; 
END; 
```
```
Tracing at level 5 or higher
Normal processing
```
Statement 19
Demonstration of $IF to control level and amount of tracing
Use $IF to Control Tracing
```
ALTER SESSION SET PLSQL_CCFLAGS =  'oe_debug:false' 
```
```
Statement processed.
```
Statement 20
Demonstration of $IF to control level and amount of tracing
Use $IF to Control Tracing
```
ALTER PROCEDURE calculate_totals COMPILE 
```
```
Procedure altered.
```
Statement 21
Demonstration of $IF to control level and amount of tracing
Use $IF to Control Tracing
```
BEGIN 
   calculate_totals; 
END; 
```
```
Normal processing
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database