The PL/SQL preprocessor directives $if … $then, $else and $end allow to selectively exclude (or include, for that matter) portions of PL/SQL when compiling it.
NOTE: it's $end, not $end if and $end is not followed by a semicolon.
Variables («inquiry directives») which are evaluated in the $if … $then part of the preprocessor directive start with two dollar symbols.
Simple example
When the following simple procedure is compiled and run, it will print true.
create or replace procedure tq84_pp
authid definer
as
begin
$if true $then
dbms_output.put_line('true' );
$else
dbms_output.put_line('false');
$end
end;
/
Different functionality on Development, UAT and Production environments
Conditional compilation can be used to implement different functionalities depending on an environment (such as dev, uat or prod) the package is run:
Env package
The «environment» package is created in three versions: one where prod is true, one where uat is true and one where dev is true and then (ideally only once) deployed to the respective destination:
create or replace package tq84_env authid definer as
prod constant boolean := false;
uat constant boolean := true;
dev constant boolean := false;
end tq84_env;
/
Functionality packages
The conditional if's in the functionality package(s) then implement differing functionality according to the values of the defined constants in the environment packages:
create or replace package body tq84_func as
procedure go is
begin
dbms_output.put('Environment is ');
$if tq84_env.prod $then
dbms_output.put_line('PROD');
$elsif tq84_env.uat $then
dbms_output.put_line('UAT');
$elsif tq84_env.def $then
dbms_output.put_line('DEV');
$else
dbms_output.put_line('undefined');
$end
end go;
end tq84_func;
/
Conditionally throw an error when compiling ($ERROR directive)
prompt create package
create or replace package tq84_error_directive as
$IF $$FLAG_VERSION_ERROR $THEN
$ERROR 'At least version 4 required' $END
$END
end;
/
show errors
prompt alter session
alter session set plsql_ccflags = 'FLAG_VERSION_ERROR:true';
prompt recompile package
alter package tq84_error_directive compile;
show errors package tq84_error_directive;
alter session set plsql_ccflags='FLAG:true';
create package tq84_package as
procedure do;
end tq84_package;
/
create package body tq84_package as
procedure do is begin
dbms_output.put_line( $IF $$FLAG $THEN 'Yes!' $ELSE 'No!' $END);
end do;
end tq84_package;
/
--connect c##user_01/pw
connect rene/rene
alter package tq84_package compile body reuse settings;
exec tq84_package.do
-- Yes!
alter package tq84_package compile reuse settings;
exec tq84_package.do
-- Yes!
alter package tq84_package compile;
exec tq84_package.do
-- No!
drop package tq84_package;
alter session set plsql_ccflags='TQ84_BOOL:true,TQ84_NUM:42';
create package tq84_package as
procedure do;
end tq84_package;
/
create package body tq84_package as
procedure do is
begin
$if $$tq84_bool $then
dbms_output.put_line('yes');
$else
dbms_output.put_line('no');
$end
dbms_output.put_line('The num is: ' || $$tq84_num);
end do;
end tq84_package;
/
exec tq84_package.do
column type format a12
column name format a20
column plsql_ccflags format a30
select
type,
name,
plsql_ccflags
--plsql_optimize_level,
--plsql_code_type,
--plsql_warnings,
--nls_length_semantics,
--plscope_settings
from
user_plsql_object_settings
where
name = 'TQ84_PACKAGE'
;
drop package tq84_package;
alter session set plsql_ccflags='NEW_FEATURE:true,SOME_NUM:42';
create package tq84_package as
procedure do;
procedure line;
procedure unit;
procedure compilation_parameters;
end tq84_package;
/
create package body tq84_package as
procedure do is
begin
dbms_output.put_line(
$IF $$NEW_FEATURE $THEN
'Congratulation, you have the new feature (line: '
$ELSE
'We recommend you upgrade to the new feature'
$END
);
end do;
procedure line is begin
dbms_output.put_line('Line is: ' || $$PLSQL_LINE);
end line;
procedure unit is begin
dbms_output.put_line('Unit is: ' || $$PLSQL_UNIT);
end unit;
procedure compilation_parameters is begin
dbms_output.put_line( 'PLSCOPE_SETTINGS: ' || $$PLSCOPE_SETTINGS );
dbms_output.put_line( 'PLSQL_CCFLAGS: ' || $$PLSQL_CCFLAGS );
dbms_output.put_line( 'PLSQL_CODE_TYPE: ' || $$PLSQL_CODE_TYPE );
dbms_output.put_line( 'PLSQL_OPTIMIZE_LEVEL: ' || $$PLSQL_OPTIMIZE_LEVEL );
dbms_output.put_line( 'PLSQL_WARNINGS: ' || $$PLSQL_WARNINGS );
dbms_output.put_line( 'NLS_LENGTH_SEMANTICS: ' || $$NLS_LENGTH_SEMANTICS );
dbms_output.put_line( 'PERMIT_92_WRAP_FORMAT: ' || $$PERMIT_92_WRAP_FORMAT );
dbms_output.put_line( 'SOME_NUM: ' || $$SOME_NUM );
end compilation_parameters;
end tq84_package;
/
begin
dbms_preprocessor.print_post_processed_source(
object_type => 'PACKAGE BODY',
schema_name => user,
object_name => 'TQ84_PACKAGE'
);
end;
/
-- What preprocessor flag was tha package compiled with?
select type, substr(plsql_ccflags, 1, 50) from user_plsql_object_settings where name = 'TQ84_PACKAGE';
--
exec tq84_package.do;
exec tq84_package.line;
exec tq84_package.unit;
exec tq84_package.compilation_parameters;
-- connect c##user_01/pw
connect rene/rene
exec tq84_package.do;
alter session set plsql_ccflags='NEW_FEATURE:false';
exec tq84_package.do;
alter package tq84_package compile;
drop package tq84_package;