8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Conditional Compilation in Oracle 10g Database Release 2

Conditional compilation allows PL/SQL code to be tailored to specific environments by selectively altering the source code based on compiler directives. It is considered a new feature of Oracle 10g Release 2, but is available in Oracle 10g Release 1 (10.1.0.4.0).

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

$IF boolean_static_expression $THEN text
  [ $ELSIF boolean_static_expression $THEN text ]
  [ $ELSE text ]
$END

As an example, let's assume that all application debugging is performed by calling a procedure called DEBUG. Conditional compilation can be used to provide an on/off switch for the debug, as well as influencing the debug information that is produced. The following procedure implements a variety of debug behavior using conditional compilation.

CREATE OR REPLACE 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;
/

The debug_on flag acts as an on/off switch, as a value of FALSE will result in an empty procedure. Assuming debug is enabled, the DBMS_DB_VERSION package is used to determine if the input text should be truncated to prevent errors in DBMS_OUTPUT. If the code is running on a Release 2 server this truncation is not necessary due to the enhancements in the DBMS_OUTPUT package. The show_date flag is used to determine if a date prefix should be added to the debug message.

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.

SQL>

The 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.

SQL>

As expected, the lack of compile flags results in 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.

SQL>

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>

The result of conditional compilation is the removal of unnecessary code. PL/SQL is an interpreted language, so removing unnecessary code may produce performance improvements in some circumstances.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.