Obsessed with Oracle PL/SQL

The RETURNING clause allows you to retrieve values of columns (and expressions based on columns) that were modified by an insert, delete or update. Without RETURNING you would have to run a SELECT statement after the DML statement is completed, in order to obtain the values of the changed columns. So RETURNING helps avoid another roundtrip to the database, another context switch in a PL/SQL block. The RETURNING clause can return multiple rows of data, in which case you will use the RETURNING BULK COLLECT INTO form. You can also call aggregate functions in the RETURNING clause to obtain sums, counts and so on of columns in multiple rows changed by the DML statement. Finally, you can also use RETURNING with EXECUTE IMMEDIATE (for dynamically constructed and executed SQL statements). Run this LiveSQL script to see all of the statements shown below "in action." First, I will create a table to use in my scripts: CREATE TABLE parts ( part_number INTEGER , part_...

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

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

Waaaaay back in 2010, on April 8 to be specific, I started a website called the PL/SQL Challenge. It featured a daily PL/SQL quiz (yes, that's right - a new quiz every weekday!) and gave Oracle Database developers a way to both deepen and demonstrate their expertise. Players were ranked and competed for top honors in our annual championships. Not quite as waaaaay back, in 2014, I rejoined Oracle Corporation after 22 years away (from the company, not from the technology). The PL/SQL Challenge came with me, and a year later we rebranded it as the Oracle Dev Gym . Today, we offer quizzes on SQL, PL/SQL, database design, logic, Java and Application Express. We've added workouts and classes. Yesterday we celebrated the ninth anniversary of the Dev Gym / PL/SQL Challenge. And my oh my but Oracle Database developers have been busy! Here are some stats from those nine years: Almost 35,000 developers and DBAs have taken quizzes on the site, a total of 1.27M answers subm...

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

This question was submitted as a comment in one of my videos today: Do we have to include an exception section for each individual subprogram or can we have a single handler for all subprograms? The quick answer is: if you want an exception raised in a procedure or function defined in a package, you need to add an exception to that  subprogram. I can certainly see why this question would come up. A package body can  have its own exception handler. Here's an example: CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER IS PROCEDURE proc; END; / CREATE OR REPLACE PACKAGE BODY pkg IS PROCEDURE proc IS BEGIN RAISE NO_DATA_FOUND; END; BEGIN NULL; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.put_line ('Was proc executed?'); END; / And it kinda, sorta looks like if I execute the following block, I will see "Was proc executed?" on my screen. BEGIN pkg.proc; END; / But I would be wrong. Instead, I will see: ORA-01403: no da...

DatelineDB: April 1st 2019 The European Union turned heads today with a surprise announcement: Starting 1 January 2020, all business logic in applications must be made available via code stored inside the database. While we recommend that you use Oracle Database and PL/SQL , that will not be required. This position was apparently taken after close review of the groundbreaking research conducted by Toon Koppelaars of Oracle Corporation, in which he showed that by putting business logic in the database, the overall work - and therefore energy consumption - of the application is reduced, sometimes by as much as 235%. While improving the overall performance of the application by 500%. A close confidant of the President of the European Union told DatelineDB that the EU would soon adopt a resolution stating that we are now in a climate emergency and every effort must be made in every aspect of human activity to slow down the warming of our planet. "So the decision to requ...