Working with Postprocessed Code - Oracle

You can use the DBMS_PREPROCESSOR package to display or retrieve the source text of your program in its postprocessed form. DBMS_PREPROCESSOR offers two programs, overloaded to allow you to specify the object of interest in various ways, as well as to work with individual strings and collections:

DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE

Retrieves the postprocessed source and then displays it with the function DBMS_OUTPUT.PUTLINE.

DBMS_PREPROCESSOR.GET_POST_PROCESSED_SOURCE

Returns the postprocessed source as either a single string or a collection of strings.

When working with the collection version of either of these programs, you will need to declare that collection based on the following package-defined collection:

  TYPE DBMS_PREPROCESSOR.source_lines_t IS TABLE OF VARCHAR2(32767)
     INDEX BY BINARY_INTEGER;

The following sequence demonstrates the capability of these programs. I compile a very small program with a selection directive based on the optimization level. I then display the postprocessed code, and it shows the correct branch of the $IF statement.

  /* File on web: cc_postprocessor.sql  
  CREATE OR REPLACE PROCEDURE post_processed
  IS
  BEGIN
  $IF $$PLSQL_OPTIMIZE_LEVEL = 1
  $THEN 
     -- Slow and easy
    NULL;
  $ELSE
     -- Fast and modern and easy
     NULL;
  $END

  END post_processed;
  /

  SQL> BEGIN
   2      DBMS_PREPROCESSOR. PRINT_POST_PROCESSED_SOURCE (
   3          'PROCEDURE', USER, 'POST_PROCESSED');
   4  END;
   5  /

  PROCEDURE post_processed
  IS
  BEGIN
  -- Fast and modern and easy
  NULL;
  END post_processed;

In the following block, I use the “get” function to retrieve the postprocessed code, and then display it using DBMS_OUTPUT.PUT_LINE:

  DECLARE
     l_postproc_code      DBMS_PREPROCESSOR. SOURCE_LINES_T;
     l_row                PLS_INTEGER;
  BEGIN
     l_postproc_code := 
   DBMS_PREPROCESSOR.GET_POST_PROCESSED_SOURCE (
      'PROCEDURE', USER, 'POST_PROCESSED');
     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;
  /

Conditional compilation opens up all sorts of possibilities for PL/SQL developers and application administrators. And its usefulness only increases as new versions of Oracle are released and the DBMS_DB_VERSION constants can be put to full use, allowing us to take full advantage of each version’s unique PL/SQL features.