CC PL/SQL- Conditional Compilation in PL/SQL

============================

General

============================

Conditional Compilation in PL/SQL (aka CC PLSQL)

Was added in Oracle 10.1.0.4

Conditional compilation allows PL/SQL code to be tailored by altering the source code based on compiler directives.  

Compiler flags are identified by the "$$" prefix

Conditional control is provided by the $IF-$THEN-$ELSE syntax.

The result of conditional compilation is the removal of code. 

PL/SQL is an interpreted language, so removing unnecessary code may produce performance improvements in some circumstances.

============================

Example

============================

Basic example

$$some_flag

$IF <condition_1> $THEN 

  do_this_01

$ELSIF <condition_2> $THEN 

  do_this_02

$ELSE 

  do_this_03

$END

Procedure example

CREATE OR REPLACE PROCEDURE debug (p_text  IN  VARCHAR2) IS

  $IF $$debug_on $THEN

    l_text  VARCHAR2(32767);

  $END

BEGIN

  $IF $$debug_on $THEN

    $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN

      l_text := SUBSTR(p_text, 1 ,233);

    $ELSE

      l_text := p_text;

    $END

    $IF $$show_date $THEN

      DBMS_OUTPUT.put_line(TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || ': ' || l_text);

    $ELSE

      DBMS_OUTPUT.put_line(p_text);

    $END

  $ELSE

    NULL;

  $END

END debug;

/

The debug_on flag acts as an on/off switch.

A value of FALSE will result in an empty procedure. 

A value of TRUE is running the IF $$debug $THEN code.

Once the procedure is compiled the complete source is stored in the database, including the conditional code directives.

SET PAGESIZE 30

SELECT text

  FROM user_source

 WHERE name = 'DEBUG'

   AND type = 'PROCEDURE';

TEXT

------------------------------------------------------------------

PROCEDURE debug (p_text  IN  VARCHAR2) AS

  $IF $$debug_on $THEN

    l_text  VARCHAR2(32767);

  $END

BEGIN

  $IF $$debug_on $THEN

    $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN

      l_text := SUBSTR(p_text, 1 ,233);

    $ELSE

      l_text := p_text;

    $END

    $IF $$show_date $THEN

      DBMS_OUTPUT.put_line(TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || ': ' || l_text);

    $ELSE

      DBMS_OUTPUT.put_line(p_text);

    $END

  $ELSE

    NULL;

  $END

END debug;

21 rows selected.

The DBMS_PREPROCESSOR.print_post_processed_source() procedure of the DBMS_PREPROCESSOR package displays the post-processed source.

SET SERVEROUTPUT ON SIZE UNLIMITED

BEGIN

  DBMS_PREPROCESSOR.print_post_processed_source (

    object_type => 'PROCEDURE',

    schema_name => 'TEST',

    object_name => 'DEBUG');

END;

/

PROCEDURE debug (p_text  IN  VARCHAR2) AS

BEGIN

NULL;

END debug;

PL/SQL procedure successfully completed.

Without compile flags, we get  an empty debug procedure. 

Setting the debug_on and show_date compiler flags results in debug messages printed with a date prefix.

ALTER PROCEDURE debug COMPILE PLSQL_CCFLAGS = 'debug_on:TRUE, show_date:TRUE' REUSE SETTINGS;

BEGIN

  DBMS_PREPROCESSOR.print_post_processed_source (

    object_type => 'PROCEDURE',

    schema_name => 'TEST',

    object_name => 'DEBUG');

END;

/

PROCEDURE debug (p_text  IN  VARCHAR2) AS

l_text  VARCHAR2(32767);

BEGIN

l_text := p_text;

DBMS_OUTPUT.put_line(TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || ': ' || l_text);

END debug;

PL/SQL procedure successfully completed.

Switching off the show_date compiler flag results in debug messages printed without a date prefix.

ALTER PROCEDURE debug COMPILE PLSQL_CCFLAGS = 'debug_on:TRUE, show_date:FALSE' REUSE SETTINGS;

SET SERVEROUTPUT ON SIZE UNLIMITED

BEGIN

  DBMS_PREPROCESSOR.print_post_processed_source (

    object_type => 'PROCEDURE',

    schema_name => 'TEST',

    object_name => 'DEBUG');

END;

/

PROCEDURE debug (p_text  IN  VARCHAR2) AS

l_text  VARCHAR2(32767);

BEGIN

l_text := p_text;

DBMS_OUTPUT.put_line(l_text);

END debug;

PL/SQL procedure successfully completed.

SQL>

============================

REUSE SETTINGS

============================

The compile-time value of Conditional Compilation parameters is stored with the metadata of the PL/SQL unit being compiled.

If you omit any parameter from this clause and you specify REUSE SETTINGS, then if a value was specified for the parameter in an earlier compilation of this PL/SQL unit, the database uses that earlier value. 

If you do not specify REUSE SETTINGS ,then the database obtains the value for that parameter from the session environment.

If that one was not set in session environment, then the parameter value is set to NULL.