Skip to main content

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

Comments

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel