CONDITIONAL COMPILATION

sayan_bappa@yahoo.co.in in ADVANCED PLSQLPLSQL_COMPILER

Oracle 10g release 2 introduces a new feature called Conditional compilation. It allows PLSQL compiler to compile a selected parts of a program based on conditions you specify with the $IF directive in PLSQL.

It enables you to customize the functionality in a PLSQL application without removing source code.

It utilizes the latest functionality with the latest database release or disable the new features to run the application against an older release of the database.

It activates the debugging or tracing functionality in the development environment and hide the functionality in the application in the production environment.

Compiler flags are identified by the "$$" prefix while the conditional control is provided by the $IF-$THEN-$ELSE-$END syntax.

Benefits of Conditional Compilation:

Support multiple version of the same program in one source code
Easy maintenance and debugging of code
Easy migration of code to a different release of the database.

How does conditional compilation works?

Selection directives: Use the $IF token
Inquiry directives: Use the $$ token
Error directives: Use the $ERROR token

We can use DBMS_DB_VERSION package to get the latest DB version. This is a package without package body. This package has two variables.

version –> Its indicates the database version
release –> Its indicates the latest release no.

Also, there are many variables which are initialized as False. But one variable is initialized as True. That indicates the current database version:

ver_le_19 constant boolean := TRUE;

So the current database version is 19.

SELECT TEXT
FROM ALL_SOURCE
WHERE LOWER(NAME) = 'dbms_db_version';

package dbms_db_version is

  version constant pls_integer :=

          19; -- RDBMS version number

  release constant pls_integer := 0;  -- RDBMS release number



  /* The following boolean constants follow a naming convention. Each

     constant gives a name for a boolean expression. For example,

     ver_le_9_1  represents version <=  9 and release <= 1

     ver_le_10_2 represents version <= 10 and release <= 2

     ver_le_10   represents version <= 10



     Code that references these boolean constants (rather than directly

     referencing version and release) will benefit from fine grain

     invalidation as the version and release values change.



     A typical usage of these boolean constants is



         $if dbms_db_version.ver_le_10 $then

           version 10 and ealier code

         $elsif dbms_db_version.ver_le_11 $then

           version 11 code

         $else

           version 12 and later code

         $end



     This code structure will protect any reference to the code

     for version 12. It also prevents the controlling package

     constant dbms_db_version.ver_le_11 from being referenced

     when the program is compiled under version 10. A similar

     observation applies to version 11. This scheme works even

     though the static constant ver_le_11 is not defined in

     version 10 database because conditional compilation protects

     the $elsif from evaluation if the dbms_db_version.ver_le_10 is

     TRUE.

  */


  /* Deprecate boolean constants for unsupported releases */


  ver_le_9_1    constant boolean := FALSE;

  PRAGMA DEPRECATE(ver_le_9_1);

  ver_le_9_2    constant boolean := FALSE;

  PRAGMA DEPRECATE(ver_le_9_2);

  ver_le_9      constant boolean := FALSE;

  PRAGMA DEPRECATE(ver_le_9);

  ver_le_10_1   constant boolean := FALSE;

  PRAGMA DEPRECATE(ver_le_10_1);

  ver_le_10_2   constant boolean := FALSE;

  PRAGMA DEPRECATE(ver_le_10_2);

  ver_le_10     constant boolean := FALSE;

  PRAGMA DEPRECATE(ver_le_10);

  ver_le_11_1   constant boolean := FALSE;

  PRAGMA DEPRECATE(ver_le_11_1);

  ver_le_11_2   constant boolean := FALSE;

  ver_le_11     constant boolean := FALSE;

  ver_le_12_1   constant boolean := FALSE;

  ver_le_12_2   constant boolean := FALSE;

  ver_le_12     constant boolean := FALSE;

  ver_le_18     constant boolean := FALSE;

  ver_le_19     constant boolean := TRUE;



end dbms_db_version;

SET SERVEROUTPUT ON;

BEGIN
  DBMS_OUTPUT.PUT_LINE('DB_VERSION -> '||DBMS_DB_VERSION.VERSION);
  DBMS_OUTPUT.PUT_LINE('RELEASE NO -> '||DBMS_DB_VERSION.RELEASE);
  IF DBMS_DB_VERSION.ver_le_19 THEN
    DBMS_OUTPUT.PUT_LINE('ITS 19C');
  END IF;
END;
/

SELECT FUN_GET_EMP_DTLS(100) EMP_DTLS FROM DUAL;

CONDITIONAL COMPILATION: Output

CREATE OR REPLACE FUNCTION FUN_GET_EMP_DTLS(P_EMPID EMPLOYEES.EMPLOYEE_ID%TYPE)
RETURN VARCHAR2
IS
  V_DTLS VARCHAR2(1000);
BEGIN
  SELECT FIRST_NAME||' '||LAST_NAME
  INTO V_DTLS
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = P_EMPID;
  --
  $IF DBMS_DB_VERSION.VERSION < 19 $THEN
    RETURN V_DTLS;
  $ELSE
    V_DTLS := 'NO DATA FOUND';
    RETURN V_DTLS;
  $END
END;
/

Now in the following example, we will check the plsql_optimize_level. If its not set at 3 then oracle will raise an error.

Now checking the current plsql_optimize_level:

  SELECT NAME||' -> '||VALUE plsql_optimize_level
  FROM V$PARAMETER
  WHERE NAME = 'plsql_optimize_level';

So it will definitely raise an error as per below code.

CREATE OR REPLACE PROCEDURE OPT_LEVEL_TEST
AS
  V_STRING VARCHAR2(100);
BEGIN
  SELECT NAME||' -> '||VALUE
  INTO V_STRING
  FROM V$PARAMETER
  WHERE NAME = 'plsql_optimize_level';
  
  DBMS_OUTPUT.PUT_LINE(V_STRING);
  
  $IF $$plsql_optimize_level <> 3 $THEN
    $ERROR 'PLSQL OPTIMIZE LEVEL SHOULD BE 3' $END
  $END
  
END;
/

CONDITIONAL COMPILATION: Output

Now setting the PLSQL_OPTIMIZE_LEVEL as 3.

Now the below procedure will be compiled successfully.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;

CREATE OR REPLACE PROCEDURE OPT_LEVEL_TEST
AS
  V_STRING VARCHAR2(100);
BEGIN
  SELECT NAME||' -> '||VALUE
  INTO V_STRING
  FROM V$PARAMETER
  WHERE NAME = 'plsql_optimize_level';
  
  DBMS_OUTPUT.PUT_LINE(V_STRING);
  
  $IF $$plsql_optimize_level <> 3 $THEN
    $ERROR 'PLSQL OPTIMIZE LEVEL SHOULD BE 3' $END
  $END
  
END;
/