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:
Retrieves the postprocessed source and then displays it with the function DBMS_OUTPUT.PUTLINE.
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
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.