View Post Processed Code (Conditional Compliation)

View All Scripts Login to Run Script

Script Name View Post Processed Code (Conditional Compliation)
Description This script demonstrates use of the DBMS_PREPROCESSOR package to display the "final" source code that was compiled from your original conditionally-compiled code. With conditional compilation, you use $IF and other $-prefixed elements to potentially include/exclude chunks for code for the compilation process. It is, therefore, critical, that you can see exactly what will be executed.
Area PL/SQL General / PL/SQL Procedures, Functions, Packages
Contributor Steven Feuerstein
Created Friday January 29, 2016

Statement 1
The default optimization level for PL/SQL is 2, but let's make sure! And now you see how to modify the level (valid values are 0 - 3).
Reinforce the Default
```
ALTER PROCEDURE post_processed 
COMPILE plsql_optimize_level=2
```
```
ORA-04043: object POST_PROCESSED does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-04043

Statement 2

A simple demonstration of conditional logic using $IF. I check the value of pre-defined conditional compilation directive and then decide which code should be compiled, based on that.

Branch Code Based on Opt Level

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;

Procedure created.

Statement 3

Use that veeeeryyy long named procedure to show the source code that was compiled. Notice that I do not simply pass USER for the second argument. In APEX, that always returns "APEX_PUBLIC_USER" - I need the name of the current schema!

Show the Code!

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

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

Statement 4

Change Optimization Level

ALTER PROCEDURE post_processed 
COMPILE plsql_optimize_level=1

Procedure altered.

Statement 5

This time, I use the package API to retrieve the code into an array, and then iterate through the array. You get to choose your approach.

View Post-Processed Code Again

DECLARE 
   l_postproc_code   DBMS_PREPROCESSOR.source_lines_t; 
   l_row             PLS_INTEGER; 
BEGIN 
   l_postproc_code := 
      DBMS_PREPROCESSOR.get_post_processed_source ( 
         'PROCEDURE', 
         SYS_CONTEXT ('userenv', 'current_schema'), 
         '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;

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

Statement 6

Sure pass it a string or array and the code will be displayed.

Print Source Code from DBMS_PREPROCESSOR

DECLARE 
   l_code     DBMS_SQL.varchar2a; 
   l_source   VARCHAR2 (32767); 
BEGIN 
     SELECT text 
       BULK COLLECT INTO l_code 
       FROM user_source 
      WHERE name = 'POST_PROCESSED' AND TYPE = 'PROCEDURE' 
   ORDER BY line; 
 
   FOR indx IN 1 .. l_code.COUNT 
   LOOP 
      l_source := l_source || l_code (indx); 
   END LOOP; 
 
   DBMS_PREPROCESSOR.print_post_processed_source (l_source); 
END;

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

Statement 7

This approach includes the line numbers and preserves the indentation and blank lines.

Reusable Utility to Display Code

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;

Procedure created.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database