DEVELOPER: PL/SQL PracticesOn Conditional CompilationBy Steven Feuerstein Apply best practices to PL/SQL conditional compilation. 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:
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 9.2.0.6 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:
Get It Right with the Error DirectiveI 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
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 ( Warning: Function created with compilation errors. SQL> SHOW ERRORS LINE/COL ERROR
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 Warning: Procedure created with compilation errors. SQL> SHOW ERRORS LINE/COL ERROR 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' Session altered. SQL> CREATE OR REPLACE PACKAGE user_types Package created. SQL> CREATE OR REPLACE PROCEDURE show_info Procedure created. SQL> ALTER PROCEDURE show_info COMPILE Warning: Procedure altered with compilation errors. SQL> show errors LINE/COL ERROR FORALL and Every Version of PL/SQLI 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 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 ( CREATE OR REPLACE PACKAGE 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
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 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. Steven Feuerstein (steven@stevenfeuerstein.com) is considered one of the world's leading experts on the Oracle PL/SQL language, having written 10 books on the subject, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (O'Reilly Media). Feuerstein serves as a senior technology advisor for Quest Software and is currently building a unit testing tool for PL/SQL programs www.unit-test.com).
|
Printer View