Obsessed with Oracle PL/SQL

This post is the fourth in my series on conditional compilation. You will find links to the entire series at the bottom. In this post, I explore how to set and use conditional compilation flags (also known as inquiry directives and referred to below as ccflags ) used in $IF statements, and control which code will be included or excluded when compilation occurs. In theory, you don't need ccflags at all. You could just create a package with static constants, like DBMS_DB_VERSION , and then reference those constants in $IF statements. That makes sense when many different compilation units (packages, procedures, triggers, functions, object types) need to be consistently controlled by the same settings. With the package approach, when you change a value for the constant, the dependent program units will be invalidated, and upon recompilation, will be compiled with the new values. If, on the other hand, you want to add conditional compilation logic to a single unit, or a handful,...

3rd in a series on conditional compilation. See end of post for links to all posts in the series. Do you write code that must run on more than one version of Oracle Database? This is almost always the case for suppliers of "off the shelf" applications. And when confronted with this reality, most developers choose between these two options: Use only those features available in all versions ("lowest common denominator" or LCD programming). or Maintain separate copies of the code for each supported version, so you can take advantage of new features in later versions of the database ("sure to create a mess" or SCAM programming). And let's face it, both have some serious drawbacks. The LCD approach ensures that your code will compile on all supported versions. But you will sacrifice the ability to take advantage of new features in the later versions. That can be a high price to pay. The SCAM approach, well, "sure to create a mess" ...

2nd in a series on conditional compilation. See end of post for links to all posts in the series. In the previous (first) post in my series on conditional compilation, I covered use cases and presented some simple examples. In this post, I show you how you can confirm what code is actually going to be executed after compilation. Without conditional compilation, this is of course a silly exercise. The code that is executed is the same as the code you see in your editor. But with conditional compilation, the code that is compiled and therefore runs could depend on any of the following: The version of the database in which it is compiled The values of user-defined conditional compilation flags The values of pre-defined (system) conditional compilation flags, like $$plsq1_optimize_level It can be a little bit nerve-wracking for a developer to not be entirely sure what is going to execute, so we provide the DBMS_PREPROCESSOR package, with its two subprograms: print_...

1st in a series on conditional compilation. See end of post for links to all posts in the series. Conditional compilation allows the compiler to compile selected parts of a program based on conditions you specify using $ syntax in PL/SQL. When you see statements like $IF, $ELSE, $END and $ERROR in your PL/SQL code, you are looking at conditional compilations, sometimes also referred to as "ifdef" processing. There's a really good chance you've never taken advantage of conditional compilation in PL/SQL, so I thought I'd write up a few blog posts about why you might want to use it - and then how  to put it to use. Conditional compilation comes in very handy when you need to do any of the following: Compile and run your PL/SQL code base on different versions of Oracle, taking advantage of features specific to those versions.  Run certain code during testing and debugging, but then omit that code from the production code. Or vice versa.  Install/compile d...