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!

The eternal question: is it a bug or a feature? The case of DBMS_ASSERT.SQL_OBJECT_NAME

Have you ever tried to execute DBMS_ASSERT.SQL_OBJECT_NAME passing a packaged function or procedure?

select dbms_assert.sql_object_name('<SCHEMA>.<PACKAGE>.<whatever>') from dual;

If <SCHEMA>.<PACKAGE> exists, you can write whatever you like and the query will return the input string as it is, that is a valid result.
It will only fail if the object <SCHEMA>.<PACKAGE> does not exist or the user calling the function does not hold sufficient privileges on that object.

ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 472
ORA-06512: at "SYS.DBMS_ASSERT", line 467
44002. 0000 -  "invalid object name"
*Document: Yes
*Cause:    The input parameter string was not a qualified
           SQL identifier of an existing SQL object.
*Action:   Make sure the string matches an existing SQL object that
           is accessible by the current schema.

So, the question is: why DBMS_ASSERT.SQL_OBJECT_NAME accepts fully qualified packaged procedure/function names but it does not check if they actually exist?

I mean, if this function would also verify that the packaged procedure or function exists, would it cause any trouble?

And in that case, why not adding an optional parameter whose default value makes the function work as usual but allows for an extended verification of the packaged procedures/functions?

So, in the end, is this a bug or a feature that I don't fully appreciate?