Enhanced simple CASE statement syntax in Oracle 23ai

Oracle 23ai extends the syntax of the so-called "simple" CASE statement in PL/SQL, that is the version of CASE where an expression appears right after the CASE keyword.
The list of WHEN blocks now allows dangling expressions (in red below), that is conditional expressions where the left operand is missing.

DECLARE
    x number;
BEGIN
    case x
    when is null THEN
      dbms_output.put_line('null');
    when 1 then 
        dbms_output.put_line('1');
        return;
    when > 1 then 
        dbms_output.put_line('greater than 1');
        return;
    else 
        dbms_output.put_line('else');
        return;
    end case;
END;
/

The PL/SQL block above would raise PL/SQL parsing errors in earlier versions (PLS-00103 on lines 5 and 10).

It is worth noting that in earlier versions the only way to catch WHEN x IS NULL, would be to replace NULL with some other value or, better, use the "searched" CASE statement instead.

For more information, please refer to the official Oracle 23ai documentation.

SDO_GEOM.SDO_INTERSECTION idiosyncrasy makes SDO_GEOM.SDO_BUFFER go south

Getting strange or unexpected results from SDO_GEOM.SDO_INTERSECTION?

Chances are that inverting the input geometries the result will be different.

This happens on Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0.

I did find some cases where the intersection of A with B returns a simple polygon whereas B intersect A returns a geometry collection, that is a mix of point, lines and polygons.

Now, if I feed the result to function SDO_GEOM.SDO_BUFFER with a small negative radius, in one case I still get a valid polygon whilst in the other case I get a NULL.

As you can imagine this is somewhat disturbing as it may lead to a completely wrong result.

One workaround would be to always perform both operations and compare the results, albeit it's not clear whichever is the correct one in case they differ and it's not even clear if I should rely on the area of the resulting polygons for considering them "equal" or "equivalent".

In theory I'd say that the intersection of A with B should always match the intersection of B with A, however this is not what's happening so I am left wondering if this is a bug or an unpleasant side effect of the algorithm being used.

What I believe is really a bug instead is the behavior of SDO_GEOM.SDO_BUFFER when the input geometry is one of these geometry collections (SDO_GTYPE = 2004).

I'll submit these case to Oracle Support and see what they say, luckily I can supply a couple of test cases for them to look at.

Accessing values stored in a global context from APEX

Have you ever tried, within an APEX application, to retrieve a value from a user-defined context accessed globally using function SYS_CONTEXT ?
Chances are that you will only retrieve NULLs.

After some testing, following a series of inexplicable results, a bell rang in my head pointing to something dealing with a few required values that a procedure launched from an APEX application needed in order to process the data.

It turns out that APEX is setting the CLIENT_IDENTIFIER value in USERENV, that is the value that you can retrieve using function SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER').
If you have a context defined as ACCESSED GLOBALLY, there are some rules explained in the documentation about what you could get given certain conditions (link for Oracle 19c).

At the end of the discussion, the documentation says that if you are getting NULLs, then you might have a problem with the CLIENT_IDENTIFIER and you need to clear it using:

DBMS_SESSION.CLEAR_IDENTIFIER;

It turned out that after clearing the CLIENT_IDENTIFIER, the missing values started finally to appear because, indeed, APEX is setting the CLIENT_IDENTIFIER with a value composed like this:
<APP_USER>:<APP_SESSION>.

Now, in my case it was sound and safe to clear the CLIENT_IDENTIFIER because my process was running in background and the associated Oracle session is closed when the job terminates, but if you need to retrieve these values as part of an interactive process run inside an APEX page, I'd say that you would be better off creating a wrapper function for SYS_CONTEXT where you :

1. save the current CLIENT_IDENTIFIER in a variable
2. clear it using DBMS_SESSION.CLEAR_IDENTIFIER
3. assign the desired value retrieved from the global context to another variable
   
4. restore the original CLIENT_IDENTIFIER using DBMS_SESSION.SET_IDENTIFIER
5. return the desired value.
   

I didn't test this suggestion yet as I am busy with other urgent stuff, so caveat emptor!