Posted by What happens when you want to serenade your partner with a romantic ditty, but you’re a database professional as well? Well obviously you would put those lyrics to paper in the only way you know how to as a true database geek…and that of course is DBMS_OUTPUT! But needless to say, your hopes of impressing said partner will be dashed as you discover how DBMS_OUTPUT chose to print your beautifully laid out prose.
SQL> set serveroutput on
SQL> begin
2 dbms_output.put_line('It had to be you.');
3 dbms_output.put_line(' It had to be you.');
4 dbms_output.put_line('');
5 dbms_output.put_line('I wandered around, and I finally found');
6 dbms_output.put_line(' and I finally found');
7 dbms_output.put_line(' the somebody who...');
8 dbms_output.put_line('');
9 dbms_output.put_line('Could make me be true');
10 dbms_output.put_line(' And could make me be blue');
11 dbms_output.put_line('');
12 dbms_output.put_line('And even be glad');
13 dbms_output.put_line(' just to be sad - thinking of you');
14 dbms_output.put_line(' thinking of you');
15 end;
16 /
It had to be you.
It had to be you.
I wandered around, and I finally found
and I finally found
the somebody who...
Could make me be true
And could make me be blue
And even be glad
just to be sad - thinking of you
thinking of you
Dear oh dear. All the indenting and spacing is stripped away, as are your hopes of a romantic dinner and a movie with your loved one 
But all hope is not lost. All you need to do is alter the SERVEROUTPUT setting slightly.
SQL> set serveroutput on format wrapped
SQL> begin
2 dbms_output.put_line('It had to be you.');
3 dbms_output.put_line(' It had to be you.');
4 dbms_output.put_line('');
5 dbms_output.put_line('I wandered around, and I finally found');
6 dbms_output.put_line(' and I finally found');
7 dbms_output.put_line(' the somebody who...');
8 dbms_output.put_line('');
9 dbms_output.put_line('Could make me be true');
10 dbms_output.put_line(' And could make me be blue');
11 dbms_output.put_line('');
12 dbms_output.put_line('And even be glad');
13 dbms_output.put_line(' just to be sad - thinking of you');
14 dbms_output.put_line(' thinking of you');
15 end;
16 /
It had to be you.
It had to be you.
I wandered around, and I finally found
and I finally found
the somebody who...
Could make me be true
And could make me be blue
And even be glad
just to be sad - thinking of you
thinking of you
And boom! You’re back in business. Romance is in the air!
Seriously though, even if you’re not trying to impress your other half with the dulcet tones of Sinatra, Connick Jr, Buble, Streisand, Bennett, Holiday et al, there is a more technical reason why you might want to ensure you preserve the spacing in your DBMS_OUTPUT.
A cool feature of PL/SQL is conditional compilation, where you can control the content of the source code in a program unit simply by setting some session or database level variables. For example, I might have a private routine that only should be deployed if I have set a variable “include_this” to true.
SQL> set serverout on
SQL> alter session set PLSQL_CCFLAGS = 'include_this:true';
Session altered.
SQL> create or replace package COND_COMP_PKG as
2 $IF $include_this $THEN
3 procedure private_procedure;
4 $END
5 procedure public_procedure;
6 end COND_COMP_PKG;
7 /
Package created.
Naturally, given that the source code is now “fluid” depending on the variable settings, I am going to want to be able to see what the “true” source code was at compilation. Querying USER_SOURCE does not give me that, because it retains the conditional compilation directives.
SQL> select text
2 from user_source
3 where name = 'COND_COMP_PKG';
TEXT
------------------------------------------
package COND_COMP_PKG as
$IF $include_this $THEN
procedure private_procedure;
$END
procedure public_procedure;
end COND_COMP_PKG;
This is where DBMS_PREPROCESSOR comes into play. It lets you see what the source as compiled would be
SQL> begin
2 dbms_preprocessor.print_post_processed_source(object_type => 'PACKAGE'
3 ,schema_name => user
4 ,object_name => 'COND_COMP_PKG');
5 end;
6 /
package COND_COMP_PKG as
procedure private_procedure;
procedure public_procedure;
end COND_COMP_PKG;
If we change our conditional compilation variables, we will see the altered source, in this case, the omission of the private routine.
SQL> alter session set PLSQL_CCFLAGS = '';
Session altered.
SQL> alter package COND_COMP_PKG compile;
Package altered.
SQL> begin
2 dbms_preprocessor.print_post_processed_source(object_type => 'PACKAGE'
3 ,schema_name => user
4 ,object_name => 'COND_COMP_PKG');
5 end;
6 /
package COND_COMP_PKG as
procedure public_procedure;
end COND_COMP_PKG;
But just like our romantic serenade, when it comes to PL/SQL it is important to capture the spacing and indentation of the text, otherwise how will we keep our love for the code? 
Thus, when you are using conditional compilation, make sure you also use the correct settings for SERVEROUTPUT.
SQL> set serverout on format wrapped
SQL> alter session set PLSQL_CCFLAGS = 'include_this:true';
Session altered.
SQL> create or replace package COND_COMP_PKG as
2 $IF $include_this $THEN
3 procedure private_procedure;
4 $END
5 procedure public_procedure;
6 end COND_COMP_PKG;
7 /
Package created.
SQL> begin
2 dbms_preprocessor.print_post_processed_source(object_type => 'PACKAGE'
3 ,schema_name => user
4 ,object_name => 'COND_COMP_PKG');
5 end;
6 /
package COND_COMP_PKG as
procedure private_procedure;
procedure public_procedure;
end COND_COMP_PKG;
SQL> alter session set PLSQL_CCFLAGS = '';
Session altered.
SQL> alter package COND_COMP_PKG compile;
Package altered.
SQL> begin
2 dbms_preprocessor.print_post_processed_source(object_type => 'PACKAGE'
3 ,schema_name => user
4 ,object_name => 'COND_COMP_PKG');
5 end;
6 /
package COND_COMP_PKG as
procedure public_procedure;
end COND_COMP_PKG;
Shout to to Patrick Barel for the motivation for this post.
Learning is not a spectator sport 
Buble is a wanna-be. He isn’t close to the others you mentioned. The PLSQL code is nice too.
LOL… fair point !
Two things.
First given this code
SQL> create or replace package COND_COMP_PKG as
2 $IF $include_this $THEN
3 procedure private_procedure;
4 $END
5 procedure public_procedure;
6 end COND_COMP_PKG;
7 /
Package created.
this wont get compiled – instead get compilation error – due to the “incorrectly” used Inquiry directive, we need $$ not single $ symbol.
something like this.
demo@XEPDB1> create or replace package COND_COMP_PKG as
2 $IF $include_this $THEN
3 procedure private_procedure;
4 $END
5 procedure public_procedure;
6 end COND_COMP_PKG;
7 /
Warning: Package created with compilation errors.
demo@XEPDB1> show err
Errors for PACKAGE COND_COMP_PKG:
LINE/COL ERROR
——– —————————————————————–
2/7 PLS-00181: unsupported preprocessor directive ‘$INCLUDE_THIS’
demo@XEPDB1> create or replace package COND_COMP_PKG as
2 $IF $$include_this $THEN
3 procedure private_procedure;
4 $END
5 procedure public_procedure;
6 end COND_COMP_PKG;
7 /
Package created.
second thing is we dont need to do alter session stuff to compile single package, plsql_ccflags are available at statement level,so we can set them like this
demo@XEPDB1> set serveroutput on format wrapped
demo@XEPDB1> exec dbms_preprocessor.PRINT_POST_PROCESSED_SOURCE(‘PACKAGE’,USER,’COND_COMP_PKG’);
package COND_COMP_PKG as
procedure public_procedure;
end COND_COMP_PKG;
PL/SQL procedure successfully completed.
demo@XEPDB1> alter package COND_COMP_PKG
2 compile
3 plsql_ccflags=’include_this:true’;
Package altered.
demo@XEPDB1> exec dbms_preprocessor.PRINT_POST_PROCESSED_SOURCE(‘PACKAGE’,USER,’COND_COMP_PKG’);
package COND_COMP_PKG as
procedure private_procedure;
procedure public_procedure;
end COND_COMP_PKG;
PL/SQL procedure successfully completed.
demo@XEPDB1>