Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Viewing Conditionally Compiled Code: What Will Be Run?

DZone 's Guide to

Viewing Conditionally Compiled Code: What Will Be Run?

In this post, I show you how you can confirm what code is actually going to be executed after compilation.

· Database Zone ·
Free Resource

2nd in a series on conditional compilation. 

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.

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 backtrace 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

Topics:
database ,tutorial ,conditionally compiled code ,plsql

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}