Conditional compilation lets one PL/SQL source include or leave out code when it is compiled, like #ifdef in C. Debug output can stay in the source and be compiled only into test builds, and code can use a newer feature only on database versions that have it. Directives start with $ and are resolved before the code is compiled.
Code for This Guide
The main examples are in the examples/advanced-plsql folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
$if condition $then code [$elsif condition $then code] [$else code] $end
Conditions use static values only: inquiry directives such as $$debug, set through the PLSQL_CCFLAGS parameter, built-in ones such as $$plsql_unit, and package constants such as DBMS_DB_VERSION.VERSION. An $error ... $end directive stops the compilation with a message.
A Debug Build
The session sets two flags, debug and trace_level, and the procedure compiles its debug branch and a version check.
Example:
alter session set plsql_ccflags = 'debug:true, trace_level:2';
create or replace procedure show_build is
begin
$if $$debug $then
dbms_output.put_line('debug build, trace level ' || $$trace_level);
$else
dbms_output.put_line('production build');
$end
$if dbms_db_version.version >= 23 $then
dbms_output.put_line('compiled on release ' || dbms_db_version.version || '.'
|| dbms_db_version.release || ' in ' || $$plsql_unit);
$end
end;
/
exec show_buildOutput:
Session altered. Procedure SHOW_BUILD compiled debug build, trace level 2 compiled on release 23.0 in SHOW_BUILD PL/SQL procedure successfully completed.
With debug set to true, only the debug line is compiled into SHOW_BUILD. The version check is also resolved at compile time: Oracle AI Database 26ai reports DBMS_DB_VERSION.VERSION as 23, since its release numbers are 23.26.
Recompile with Other Flags
The flags are stored with each unit when it is compiled. Recompiling one unit with new flags changes which code it contains, without touching the source.
Example:
-- recompile the same source with different flags, keeping other settings alter procedure show_build compile plsql_ccflags = 'debug:false' reuse settings; exec show_build select name, plsql_ccflags from user_plsql_object_settings where name = 'SHOW_BUILD';
Output:
Procedure SHOW_BUILD altered. production build PL/SQL procedure successfully completed. NAME PLSQL_CCFLAGS _____________ ________________ SHOW_BUILD debug:false
Compiled with debug:false, the same source now prints the production line. REUSE SETTINGS keeps the unit's other compiler settings. USER_PLSQL_OBJECT_SETTINGS shows the flags each unit was compiled with. The example drops the procedure afterward.
Things to Know
- A flag that is not set evaluates to NULL, so $if $$debug $then is false when debug is missing.
- Code in a branch that is not selected is never compiled, so it can use features the database does not have.
- Set PLSQL_CCFLAGS with ALTER SESSION for everything you compile next, or with ALTER ... COMPILE for one unit.
Related Guides
Conclusion
Conditional compilation includes or leaves out PL/SQL code at compile time with $IF directives. Drive it with PLSQL_CCFLAGS for debug and feature switches and with DBMS_DB_VERSION for version checks, and recompile a unit with new flags to change what it contains.
