Conditional Compilation in PL/SQL With DBMS_DB_VERSION and Preprocessor Directives

Writing Code That Adapts to the Database Version

If you maintain PL/SQL code that runs on multiple Oracle versions, you have probably written version checks at runtime. Conditional compilation, available since Oracle 10g Release 2, lets you handle version differences at compile time. Code for unsupported versions is excluded from the compiled unit entirely, so it does not generate runtime errors and carries no overhead.

Using DBMS_DB_VERSION

CREATE OR REPLACE FUNCTION get_employee_json(p_emp_id IN NUMBER) RETURN CLOB
IS
    l_result CLOB;
BEGIN
    $IF DBMS_DB_VERSION.VERSION >= 21 $THEN
        -- Use SQL macro or JSON_SERIALIZE (21c+)
        SELECT JSON_SERIALIZE(
            JSON_OBJECT('id' VALUE employee_id,
                        'name' VALUE first_name || ' ' || last_name,
                        'salary' VALUE salary)
        ) INTO l_result
          FROM employees WHERE employee_id = p_emp_id;
    $ELSIF DBMS_DB_VERSION.VERSION >= 12 $THEN
        -- Use JSON_OBJECT (12c+)
        SELECT JSON_OBJECT('id' VALUE employee_id,
                           'name' VALUE first_name || ' ' || last_name,
                           'salary' VALUE salary)
          INTO l_result
          FROM employees WHERE employee_id = p_emp_id;
    $ELSE
        -- Manual JSON construction for 11g
        SELECT '{"id":' || employee_id ||
               ',"name":"' || first_name || ' ' || last_name ||
               '","salary":' || salary || '}'
          INTO l_result
          FROM employees WHERE employee_id = p_emp_id;
    $END
    RETURN l_result;
END;
/

Custom Preprocessor Flags

You can define your own compilation flags using ALTER SESSION SET PLSQL_CCFLAGS or ALTER PACKAGE COMPILE PLSQL_CCFLAGS:

ALTER SESSION SET PLSQL_CCFLAGS = 'debug_mode:TRUE, logging_level:3';

CREATE OR REPLACE PROCEDURE process_order(p_order_id IN NUMBER)
IS
BEGIN
    $IF $$debug_mode $THEN
        DBMS_OUTPUT.PUT_LINE('Processing order: ' || p_order_id);
    $END

    -- Normal processing logic
    UPDATE orders SET status = 'PROCESSED' WHERE order_id = p_order_id;

    $IF $$logging_level >= 2 $THEN
        log_activity('ORDER_PROCESSED', p_order_id);
    $END
END;

Practical Uses

Use conditional compilation for version-portable code that must run on multiple database versions, debug instrumentation that should have zero overhead in production, feature toggles that control which code paths are compiled, and environment-specific behavior like different logging levels for development versus production.

Querying Compilation Settings

SELECT name, plsql_ccflags
  FROM user_plsql_object_settings
 WHERE plsql_ccflags IS NOT NULL;