Code with conditional compilation directives is not the code that gets compiled: the preprocessor first removes the branches that do not apply. DBMS_PREPROCESSOR shows the result, the source exactly as the compiler sees it, for a stored unit or for any source text.
Code for This Guide
The main examples are in the examples/pkg-utilities 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
-- print the processed source of a stored unit dbms_preprocessor.print_post_processed_source(object_type, schema_name, object_name); -- return processed lines, for a stored unit or for source text v_lines := dbms_preprocessor.get_post_processed_source(source_text);
See What Was Compiled
SHOW_MODE prints debug or release depending on the debug flag, and the session compiles it with debug set to false.
Example:
alter session set plsql_ccflags = 'debug:false';
create or replace procedure show_mode is
begin
$if $$debug $then
dbms_output.put_line('debug');
$else
dbms_output.put_line('release');
$end
end;
/
exec dbms_preprocessor.print_post_processed_source('PROCEDURE', user, 'SHOW_MODE')Output:
Session altered.
Procedure SHOW_MODE compiled
procedure show_mode is
begin
dbms_output.put_line('release');
end;
PL/SQL procedure successfully completed.Only the release line remains. The directive lines are replaced by blank lines, so line numbers in error messages still match the original source. The example drops the procedure afterward.
Process Source Text
GET_POST_PROCESSED_SOURCE can also take source text that is not stored yet, and returns it as a table of lines.
Example:
declare
v_lines dbms_preprocessor.source_lines_t;
begin
v_lines := dbms_preprocessor.get_post_processed_source(q'[procedure p is
begin
$if dbms_db_version.version >= 23 $then
null; -- new code
$else
null; -- old code
$end
end;]');
for i in 1 .. v_lines.count loop
dbms_output.put_line(rtrim(v_lines(i), chr(10))); -- each line ends in a newline
end loop;
end;
/Output:
procedure p is
begin
null; -- new code
end;
PL/SQL procedure successfully completed.On this database DBMS_DB_VERSION.VERSION is 23, so the new-code branch is kept. Each returned line ends with a newline, which the example trims before printing.
Things to Know
- Stored units are processed with the flags they were compiled with; source text is processed with the session's current PLSQL_CCFLAGS.
- Source text starts with the unit itself, such as procedure p is, without CREATE OR REPLACE.
- PRINT_POST_PROCESSED_SOURCE writes through DBMS_OUTPUT, so turn server output on first.
Related Guides
Conclusion
DBMS_PREPROCESSOR shows PL/SQL after conditional compilation, as the compiler sees it. Print the processed source of a stored unit to check which branches it contains, or process source text to test directives before you deploy.
