a couple obviously in love riding a single bike together through a field on a summer day

DBMS_OUTPUT … A (formatting) love story

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 Sad smile

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? SmileThus, 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.

3 comments

  1. 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>

Leave a Reply to Carol D. Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.