Obsessed with Oracle PL/SQL

Huh, what? Make sure my code fails  to compile? Why would I want to do that. Well, suppose that you had a compute-intensive procedure that ran every hour and benefited greatly from full PL/SQL compiler optimization (level set to 3, to take advantage of subprogram inlining and everything else it does). Next, suppose that somehow as the procedure (newly enhanced, fully tested) was being deployed to production, the optimization level was mistakenly set to 0 or 1. This would cause severe performance problems. So in that case, wouldn't it be nice if you could build a "circuit breaker" into that procedure so that the compiler says "No go" even if the code itself compiles just fine? I think it would be nice - and you can accomplish precisely that with the error directive of the conditional compilation feature of PL/SQL. First, here's the code that demonstrates precisely the scenario outlined above. CREATE OR REPLACE PROCEDURE compute_intensive AUTH...

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...