What is PL/SQL conditional compilation, how does it work, and how can I learn more about it?
Oracle Database 10g has been groundbreaking when it comes to the world of PL/SQL development, most notably in the evolution of the PL/SQL compiler. This PL/SQL Practices column focuses on the Oracle Database 10g Release 2 feature known as conditional compilation.
Oracle Database 10g Release 1 offered an optimizing compiler and compile-time warnings. The PL/SQL optimizer automatically transforms code so that it can run more efficiently (this optimizer is different from the cost-based optimizer, which Oracle Database uses to optimize the execution of SQL statements). Oracle estimates that you can expect to see your PL/SQL statements execute, on average, in half the time they took in earlier versions of Oracle Database. (Note that the PL/SQL compiler affects only PL/SQL statements, not SQL statements, within your programs).
The compile-time warnings give you feedback on programs that might compile but could still be improved. These warnings include recommendations on how and when to apply the NOCOPY parameter hint to reduce parameter-passing overhead, identify functions that contain one or more branches of logic that will not execute a RETURN statement, and much more.
Oracle Database 10g Release 2 takes PL/SQL another big step forward, by adding support for conditional compilation. We can now use conditional compilation to direct the compiler to conditionally include or exclude selected parts of a program, based on conditions specified with conditional compilation directives, statements, and identifiers prefixed by a dollar sign ($).
One particularly nice aspect of conditional compilation is that text that is excluded during conditional compilation need not be legal PL/SQL; this gives you tremendous flexibility when writing programs that need to work differently in different versions of Oracle Database.
There are many ways PL/SQL conditional compilation can benefit your application development process. The following are a few of the most common ways to apply PL/SQL conditional compilation to your applications:
Write a single program unit that automatically takes advantage of version-specific features of Oracle Database. You no longer need to maintain the same basic logic in multiple files, one for Oracle9i Database and another for Oracle Database 10g, or rely on SQL*Plus substitution variable logic to achieve the same effect.
Run or expose portions of your code during testing, and then hide that code when it goes into production. This technique is particularly valuable for tracing code execution and unit-testing private package subprograms.
Improve the maintainability of your PL/SQL code base, by soft-coding elements of your application that would otherwise have to be hard-coded as literals.
To take advantage of conditional compilation, add compiler directives (commands) to your code. The PL/SQL compiler will then evaluate the directives before compilation occurs and determine which parts of your program text cause the generation of executable code. The modified source code is then passed to the compiler for compilation.
There are three types of directives:
Selection directives. Use the $IF directive to evaluate expressions and determine which code should be included in the compiled code.
Inquiry directives. Use the $$ identifier syntax to refer to conditional compilation flags. These inquiry directives can be referenced within an $IF directive or used independently in your code.
Error directives. Use the $ERROR directive to report compilation errors based on conditions evaluated when Oracle Database prepares your code for compilation.
Oracle has also added two packages to support conditional compilation: the DBMS_DB_VERSION package, which you can use to analyze the version (in both absolute and relative terms) of the instance of Oracle Database to which you are connected, and the DBMS_PREPROCESSOR package, which allows you to see your code after all conditional compilation directives have been executed.
Availability of conditional compilation. PL/SQL conditional compilation is available in Oracle Database 10g Release 1 and later (from 10.1.0.4 onward). In Oracle Database 10g Release 2, conditional compilation is enabled by default, and it cannot be disabled.
In Oracle Database 10g Release 1, conditional compilation is enabled by default, but you can disable it by setting an underscore parameter. Conditional compilation is also supported in Oracle9i Database Release 2 (from 22.214.171.124 onward); it is disabled by default, but you can enable it by setting an underscore parameter.
In order to disable conditional compilation in Oracle Database 10g Release 1 or enable it in Oracle9i Database, contact Oracle Support for information on the conditional compilation underscore parameter.
Get more information on conditional compilation. This column offers some insights into conditional compilation, by answering reader questions. You will certainly want to study this feature in more depth before applying it to your application code base. I suggest that you check out the following:
Conditional Compilation in Oracle Database 10 g Release 2 . Hands down the best elaboration of conditional compilation, this white paper by PL/SQL Product Manager Bryn Llewellyn offers 96 pages of detailed explanations and examples, including a case study of how to use conditional compilation with unit testing. Find it at oracle.com/technetwork/tech/pl_sql/pdf/Plsql_Conditional_Compilation.pdf .
Oracle documentation on conditional compilation. Oracle Database PL/SQL User's Guide and Reference is available at download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/fundamentals.htm#BABIHIHF .
My PL/SQL programming book. Oracle PL/SQL Programming , 4th Edition, by Steven Feuerstein with Bill Pribyl: Chapter 20, "Managing PL/SQL Code," explores conditional compilation and offers several examples.
I often find myself working on multiple program units, fixing some and developing others anew. As I move around between them, I sometimes have to leave work half finished. What do you think is the best way to indicate that this program unit is incomplete and keep track of what needs to be done?
Include to-do lists in your program source code!
You could come up with a standard comment indicating "incomplete, please finish"—something like this:
/* INCOMPLETE - START
Note to self: add logic to
iterate through collection
/* INCOMPLETE - END */
You can then search your files for the comment indicator. One problem with this approach, however, is that your program, although incomplete, might still compile, giving you or someone else a mistaken impression of the state of that code.
It would certainly be better to be able to not only include a comment about the state of your code but also stop it from compiling, so that you are immediately and unambiguously informed about the state of your program. You can do this with the conditional compilation error directive, $ERROR.
Here is an example. I am writing a program to parse a delimited string into a collection. I can't finish the final SUBSTR operation at this time and want to remind myself about it later. So I use the $ERROR directive to remind me of the task and also use the special inquiry directives, $$PLSQL_UNIT and $$PLSQL_LINE, to show the name of the program unit and the line number, when I try to compile the program.
Listing 1 shows the list_to_collection function and the results of attempting compilation.
Code Listing 1: _LIST_TO_COLLECTION function and attempted compilation
SQL> CREATE OR REPLACE FUNCTION list_to_collection ( 2 string_in IN VARCHAR2 3 , delimiter_in IN VARCHAR2 DEFAULT ',' 4 ) 5 RETURN DBMS_SQL.varchar2a 6 IS 7 l_next_location PLS_INTEGER := 1; 8 l_start_location PLS_INTEGER := 1; 9 l_return DBMS_SQL.varchar2a; 10 BEGIN 11 IF string_in IS NOT NULL 12 THEN 13 WHILE ( l_next_location > 0 ) 14 LOOP 15 -- Find the next delimiter 16 l_next_location := 17 NVL (INSTR ( string_in, delimiter_in, l_start_location ), 0); 18 19 IF l_next_location = 0 20 THEN 21 -- No more delimiters, go to end of string 22 l_return ( l_return.COUNT + 1 ) := 23 SUBSTR ( string_in, l_start_location ); 24 ELSE 25 $ERROR 26 'list_to_collection INCOMPLETE! 27 Finish extraction of next item from list. 28 Go to ' || $$PLSQL_UNIT || ' at line ' || $$PLSQL_LINE 29 $END 30 END IF; 31 l_start_location := l_next_location + 1; 32 END LOOP; 33 END IF; 34 RETURN l_return; 35 END list_to_collection; 36 /
Warning: Function created with compilation errors.
SQL> SHOW ERRORS Errors for FUNCTION LIST_TO_COLLECTION: LINE/COL ERROR -------- --------------------------------------------------------------- 25/13 PLS-00179: $ERROR: list_to_collection INCOMPLETE! Finish extraction of next item from list. Go to LIST_TO_COLLECTION at line 28
The error directive is, by the way, handy under all sorts of circumstances. Suppose, for example, that I would like to make sure that a particularly complex and compute-intensive program is always compiled with the maximum optimization level. I can simply include a selection directive that checks the value of the PL/SQL compilation parameter with the $$PLSQL_OPTIMIZE_LEVEL ccflag, combined with the error directive, in the definition of the program unit, as shown in Listing 2.
Code Listing 2: COMPUTE_INTENSIVE_PROGRAM
SQL> CREATE OR REPLACE PROCEDURE compute_intensive_program 2 IS 3 BEGIN 4 $IF $$PLSQL_OPTIMIZE_LEVEL <> 2 5 $THEN 6 $ERROR 7 'compute_intensive_program must be compiled with maximum optimization!' 8 $END 9 $END 10 NULL; -- Lots and lots of code here... 11 END compute_intensive_program; 12 /
Warning: Procedure created with compilation errors.
SQL> SHOW ERRORS Errors for PROCEDURE COMPUTE_INTENSIVE_PROGRAM: LINE/COL ERROR< -------- ----------------------------------------------------------- 6/4 PLS-00179: $ERROR: compute_intensive_program must be compiled with maximum optimization!
Finally, you should consider using the error directive to catch "case not found" situations. Listing 3 shows an example. It relies on the ALTER SESSION command to set the value for a ccflag, which can then be referenced within a selection directive.
Code Listing 3: Using the error directive to catch "case not found"
SQL> ALTER SESSION SET PLSQL_CCFLAGS = 'current_user_type:1' 2 / Session altered. SQL> CREATE OR REPLACE PACKAGE user_types 2 IS 3 administrator CONSTANT PLS_INTEGER := 1; 4 enduser CONSTANT PLS_INTEGER := 2; 5 END user_types; 6 / Package created. SQL> CREATE OR REPLACE PROCEDURE show_info 2 IS 3 BEGIN 4 $IF $$current_user_type = user_types.administrator 5 $THEN 6 DBMS_OUTPUT.PUT_LINE ('Administrator!'); 7 $ELSIF $$current_user_type = user_types.enduser 8 $THEN 9 DBMS_OUTPUT.PUT_LINE ('End user!'); 10 $ELSE 11 $ERROR 'Current user type of ' || $$current_user_type || ' is not known.' $END 12 $END 13 END show_info; 14 / Procedure created. SQL> ALTER PROCEDURE show_info COMPILE 2 PLSQL_CCFLAGS = 'current_user_type:0' 3 REUSE SETTINGS 4 / Warning: Procedure altered with compilation errors. SQL> show errors Errors for PROCEDURE SHOW_INFO: LINE/COL ERROR ------- ---------------------------------------------------------- 11/4 PLS-00179: $ERROR: Current user type of 0 is not known.FORALL and Every Version of PL/SQL
I love the FORALL feature that Oracle added to PL/SQL in Oracle8i Database. I use it whenever possible to implement high-speed DML processing. I am also writing code that must work on Oracle9i Database and Oracle Database 10g. I was very excited to see that Oracle Database 10g offers the INDICES OF and VALUES OF clauses to allow me to use FORALL with sparsely filled collections. I'd love to take advantage of FORALL, but I don't want to maintain two different sets of code, one for Oracle9i Database and the other for Oracle Database 10g. How would you suggest I do that?
Take full advantage of every version of PL/SQL. Yes, FORALL is quite wonderful, isn't it? And you should take maximum advantage of every nuance of that feature—but managing multiple sets of code is a real chore.
Sounds like a job for conditional compilation, along with the new DBMS_DB_VERSION package. This package contains a set of constants that provide both absolute and relative information about the version of Oracle Database in which you are executing your code.
For example, in Oracle Database 10g Release 2, this package is defined as shown in Listing 4.
Code Listing 4: DBMS_DB_VERSION package definition
CREATE OR REPLACE package dbms_db_version is version constant pls_integer := 10; -- RDBMS version number release constant pls_integer := 2; -- RDBMS release number ver_le_9_1 constant boolean := FALSE; ver_le_9_2 constant boolean := FALSE; ver_le_9 constant boolean := FALSE; ver_le_10_1 constant boolean := FALSE; ver_le_10_2 constant boolean := TRUE; ver_le_10 constant boolean := TRUE; end dbms_db_version; /
Now let's apply this package to help us write one program that will automatically use the INDICES OF clause of FORALL if it is available.
First, I create a table and a package specification that contains a procedure that facilitates the bulk insert of rows into the table:
CREATE TABLE otn_demo (
num NUMBER, name VARCHAR2(100))
CREATE OR REPLACE PACKAGE
IS TABLE OF otn_demo%ROWTYPE
INDEX BY PLS_INTEGER;
PROCEDURE insert_rows (
rows_in IN otn_demo_aat);
Next, the package body: The insert_rows procedure uses INDICES OF if I am running Oracle Database 10g or higher. If I am running Oracle9i Database Release 2 or earlier, I copy the contents of my possibly sparse collection over to a densely filled collection. In both cases, I take advantage of record-level inserts, as shown in Listing 5.
Code Listing 5: OTN_DEMO_INSERT package body
CREATE OR REPLACE PACKAGE BODY otn_demo_insert IS PROCEDURE insert_rows ( rows_in IN otn_demo_aat ) IS BEGIN $IF DBMS_DB_VERSION.VER_LE_9_2 $THEN DECLARE l_dense otn_demo_aat; l_index PLS_INTEGER := rows_in.FIRST; BEGIN WHILE (l_index IS NOT NULL) LOOP l_dense (l_dense.COUNT + 1) := rows_in (l_index); l_index := rows_in.NEXT (l_index); END LOOP; FORALL indx IN 1 .. l_dense.COUNT INSERT INTO otn_demo VALUES l_dense (indx); END; $ELSE FORALL indx IN INDICES OF rows_in INSERT INTO otn_demo VALUES rows_in (indx); $END END insert_rows; END otn_demo_insert; /
So I have one program unit to maintain but a code base that automatically takes maximum advantage of the available set of features. Nice stuff!
Let's look at one more example. Oracle Database 10g introduced new floating-point datatypes, BINARY_FLOAT and BINARY_DOUBLE. They offer improved performance for heavy number crunching. The following program automatically declares a local variable to be BINARY_FLOAT if the datatype is available. Otherwise, it settles for the traditional NUMBER.
CREATE OR REPLACE PROCEDURE crunch_numbers IS n $IF DBMS_DB_VERSION.VER_LE_9_2 $THEN NUMBER; $ELSE BINARY_FLOAT; $END BEGIN $IF DBMS_DB_VERSION.VER_LE_9_2 $THEN n := 1.0; $ELSE n := 1.0f; $END DBMS_OUTPUT.put_line ( n ); END crunch_numbers; /
Note that in this case, I use conditional compilation to interrupt a regular statement: The selection of the datatype occurs within the declaration of the variable n. I don't have to use conditional compilation only to choose between two or more intact, executable statements.
For more questions and answers on PL/SQL conditional compilation, check out Best Practice PL/SQL.Next Steps
READ more about conditional compilation
Oracle Database PL/SQL User's Guide and Reference
white paper and presentation
DOWNLOAD Oracle Database 10g
Photography by Dmitri Popov, Unsplash