Working With PL/SQL Code - Working with Postprocessed Code
(Page 3 of 4 )
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.
Next: Testing PL/SQL Programs >>
More Oracle Articles
More By O'Reilly Media
|
 This article is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O'Reilly; ISBN: 0596009771). Check it out today at your favorite bookstore. Buy this book now.
|
|
