Obsessed with Oracle PL/SQL

Yes. No. Sort of.  It's (not all that) complicated. This question hit my Twitter feed yesterday: When you enable all warnings, have you ever seen a "PLW-06006-- uncalled procedure removed" (lots of them), when they surely are called? Now that, I must admit, has to be a little bit concerning. You write code, you know  it is going to, or should be, executed, and yet the PL/SQL compiler tells you it's been removed ? OK, OK, calm down. Everything is just fine. Here's the explanation: The optimizer performed an inlining optimization, so all the code for that procedure (or function) was moved to where it is invoked. The "original" nested or private subprogram that you wrote (and, don't worry, is still and always will be in the source code of your program unit) is, truth be told, never going to be called.  So then the compiler removed it (did not include it in the compiled code - which is not PL/SQL code any longer). Let's take a loo...

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

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

A recent Twitter thread delved into the topic of the best way to enable PL/SQL warnings for program units, including this recommendation from Bryn Llewellyn , Distinguished Product Manager for PL/SQL: which then led to Bryn volunteering me to delve into the details of PL/SQL compiler settings in an AskTOM PL/SQL Office Hours session.  Which I will do. But I figured I could start right off by writing this post. So let's explore how to set and modify PL/SQL compiler settings. First, you might wonder what those settings are or could be. The best way to check is by examining the USER_PLSQL_OBJECT_SETTINGS view (and of course the ALL* version to examine attributes of code you do not own but can execute): The values that are stored for a PL/SQL unit are set every time it is compiled—in response to "create", "create or replace", "alter", invoking a utility like Utl_Recomp, or implicitly as a side effect of trying to execute an invalid PL...

As my 25th winter in Chicago approaches (and after the 2016 elections),, I attempt to cheer myself up with jokes. Programmer jokes. Jokes that largely have to do with being too lazy to verify assumptions or prove claims before making decisions that turn out to be really bad decision.  Here's the most recent "joke" I heard from a developer, via an email back in July: We just encountered a PL/SQL performance problem after migrating a database from 10g to 11g and I tought it might interest you. I isolated the problem using    the 10046 traces and DBMS_PROFILER. So I was able to reproduce the problem with a very simple PL/SQL testcase, but I cannot explain it. He then pasted in 439 lines of code and output. So this is the "very simple" testcase? It might be, but still I wrote back: "It may be simple, but it's long and I'd appreciate it if you would summarize for me what you believe you have discovered." This way, at least, I ...