Obsessed with Oracle PL/SQL

This odd little piece of code was featured in the weekly PL/SQL Challenge quiz 12 March - 18 March 2016.

What do you think will be displayed after executing the following block?
DECLARE my_flag BOOLEAN; BEGIN CASE my_flag WHEN my_flag IS NULL THEN DBMS_OUTPUT.PUT_LINE ('my_flag is NULL'); WHEN TRUE THEN DBMS_OUTPUT.PUT_LINE ('my_flag is TRUE'); ELSE DBMS_OUTPUT.PUT_LINE ('my_flag is FALSE'); END CASE; END; / At first glance (if you are like me), you would say "my_flag is NULL", right?

After all, my_flag is initialized to NULL when declared, and I don't change the value.

But, lo and behold, you will see:
my_flag is FALSE Curious, right?

So what's going on? Well, we have a very confused and confusing piece of code: I have written a simple CASE (which is of the form CASE expression WHEN ...), but then my WHEN clauses follow a typical searched CASE format (CASE WHEN expr1 ... WHEN …

Sometime in the next couple of months, someone will submit the 1,000,000th answer on the PL/SQL Challenge. And Chris Saxon, member of the AskTom Answer Team, SQL Wizard and Database Design Quizmaster, assures me that he will write a query to identify exactly who that person is (will be). That's a very nice milestone, so we figured it will be a good time to celebrate.  We are still sorting out precisely what we will do, but for sure, we will want to feature stories from our players: How the PL/SQL Challenge has helped you in your careerHigh points (or low points) from your activity on the site (I have low points, which I will share, hopefully none of you do!)Interesting stories from the last six years of answering and commenting on quizzesWhatever else comes to mind!You are welcome to post your stories here or the PL/SQL Challenge blog or email them directly to steven.feuerstein@oracle.com. I will be collating them for publication on the site. I would also be very pleased to do some vid…

As of Oracle Database 12c Release 1, you can now use the TABLE operator with associative arrays whose types are declared in a package specification. Prior to 12.1, this was only possible with schema-level nested table and varray types. Here's a quick example:

CREATE OR REPLACE PACKAGE aa_pkg AUTHID DEFINER IS TYPE record_t IS RECORD (nm VARCHAR2 (100), sal NUMBER); TYPE array_t IS TABLE OF record_t INDEX BY PLS_INTEGER; FUNCTION my_array RETURN array_t; END; / CREATE OR REPLACE PACKAGE BODY aa_pkg IS FUNCTION my_array RETURN array_t IS l_return array_t; BEGIN l_return (1).nm := 'Me'; l_return (1).sal := 1000; l_return (200).nm := 'You'; l_return (200).sal := 2; RETURN l_return; END; END; / DECLARE l_array aa_pkg.array_t; BEGIN l_array := aa_pkg.my_array; FOR rec IN ( SELECT * FROM TABLE (l_array) ORDER BY nm) LOOP DBMS_OUTPUT.put_line (rec.nm); END LOOP; END; /
And with nested tables and …

1. Exceptions raised in the declaration section are not handled in the exception section.

This sometimes surprises a developer new to PL/SQL. The exception section of a PL/SQL block can only possibly handle an exception raised in the executable section. An exception raised in the declaration section (in an attempt to assign a default value to a variable or constant) always propagates out unhandled to the enclosing block.
Verify on LiveSQL
Exceptions Raised in Declaration Section Not Handled Locally
2. An exception raised does not automatically roll back uncommitted changes to tables.
Any non-query DML statements that complete successfully in your session are not rolled back when an exception occurs - either directly in PL/SQL or propagated out from the SQL engine. You still have the option of either committing or rolling back yourself.
If, however, the exception goes unhandled out to the host environment, a rollback almost always occurs (this is performed by the host environment).
Verify …