Viewing conditionally compiled code: what will be run?

2nd in a series on conditional compilation. See end of post for links to all posts in the series.

In the previous (first) post in my series on conditional compilation, I covered use cases and presented some simple examples.

In this post, I show you how you can confirm what code is actually going to be executed after compilation. Without conditional compilation, this is of course a silly exercise. The code that is executed is the same as the code you see in your editor.

But with conditional compilation, the code that is compiled and therefore runs could depend on any of the following:

• The version of the database in which it is compiled
• The values of user-defined conditional compilation flags
• The values of pre-defined (system) conditional compilation flags, like $$plsq1_optimize_level

It can be a little bit nerve-wracking for a developer to not be entirely sure what is going to execute, so we provide the DBMS_PREPROCESSOR package, with its two subprograms:

• print_post_processed_source - display the post-processed code on your screen
• get_post_processed_source - return the post-processed code as an array.

If you are wondering why the name of the package contains "preprocessor" but its only subprograms contain "post_processed"....well, what's life without a mystery or two? :-)

The "print" procedure has three overloadings:

1. Prints post-processed source text of a stored PL/SQL unit:

DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE (
   object_type    IN VARCHAR2,
   schema_name    IN VARCHAR2,
   object_name    IN VARCHAR2);

2. Prints post-processed source text of a compilation unit:

DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE (
   source IN VARCHAR2);
 
3. Prints post-processed source text of an INDEX-BY table containing the source text of the compilation unit:

DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE (
   source IN source_lines_t);

Let's try it out. My optimization level is set to the default: 2. I execute these statements:

CREATE OR REPLACE PROCEDURE post_processed
IS
BEGIN
   $IF $$plsql_optimize_level = 1
   $THEN
      -- Slow and problematic
      NULL;
   $ELSE
      -- Fast and modern and easy
      NULL;
   $END
END post_processed;
/

BEGIN
   DBMS_PREPROCESSOR.print_post_processed_source (
      'PROCEDURE',
      SYS_CONTEXT ('userenv', 'current_schema'),
      'POST_PROCESSED');
END;
/

and I see this output:

PROCEDURE post_processed
IS
BEGIN
   




      -- Fast and modern and easy
      NULL;
   
END post_processed;

I then set the optimization level to 1 for this procedure as I recompile it:

ALTER PROCEDURE post_processed COMPILE plsql_optimize_level=1
/

The output then changes as follows:

BEGIN
   DBMS_PREPROCESSOR.print_post_processed_source (
      'PROCEDURE',
      SYS_CONTEXT ('userenv', 'current_schema'),
      'POST_PROCESSED');
END;
/

PROCEDURE post_processed
IS
BEGIN
   

      -- Slow and problematic
      NULL;
   



END post_processed;

Take a close and careful look at these two sets of output. Notice how carefully the white space (vertical - lines, and horizontal - spaces) is preserved. This is critical.

This is the code that will be executed. So PL/SQL needs to make sure that if an exception is raised and error stack or back trace is displayed/logged, the line and column numbers in those messages reflect what you see in your original source code, namely:

CREATE OR REPLACE PROCEDURE post_processed
IS
BEGIN
   $IF $$plsql_optimize_level = 1
   $THEN
      -- Slow and problematic
      NULL;
   $ELSE
      -- Fast and modern and easy
      NULL;
   $END
END post_processed;

I hope you can see that is, in fact, the case.

I could show you examples of calling the other overloadings of the print procedure, but I think you get the idea. Here's essentially the same behavior as the print procedure, but using the get function instead - and encapsulate into my very own procedure, adding line numbers:

CREATE PROCEDURE show_code_for (tp IN VARCHAR2, nm IN VARCHAR2)
IS
   l_postproc_code   DBMS_PREPROCESSOR.source_lines_t;
   l_row             PLS_INTEGER;
BEGIN
   l_postproc_code :=
      DBMS_PREPROCESSOR.get_post_processed_source (
         tp,
         SYS_CONTEXT ('userenv', 'current_schema'),
         nm);
   l_row := l_postproc_code.FIRST;

   WHILE (l_row IS NOT NULL)
   LOOP
      DBMS_OUTPUT.put_line (
            LPAD (l_row, 3)
         || ' - '
         || RTRIM (l_postproc_code (l_row), CHR (10)));
      l_row := l_postproc_code.NEXT (l_row);
   END LOOP;
END;
/

and I put it to use:

BEGIN
   show_code_for ('PROCEDURE', 'POST_PROCESSED');
END;
/

  1 - PROCEDURE post_processed
  2 - IS
  3 - BEGIN
  4 -    
  5 - 
  6 -       -- Slow and problematic
  7 -       NULL;
  8 -    
  9 - 
 10 - 
 11 - 
 12 - END post_processed;

I hope you find this helpful.

Resources

Comprehensive white paper: a great starting place - and required reading - for anyone planning on using conditional compilation in production code

Conditional compilation scripts on LiveSQL

Tim Hall (Oracle-BASE) coverage of conditional compilation

Conditional compilation documentation

My Oracle Magazine article on this topic

Conditional Compilation Series
1. An introduction to conditional compilation
2. Viewing conditionally compiled code: what will be run?
3. Writing code to support multiple versions of Oracle Database
4. Setting and using your own conditional compilation flags
5. How to make sure your code FAILS to compile