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?

ORA-08104: this index object ... is being online built or rebuilt

If you are getting this error while trying to execute DROP INDEX after unsuccessfully trying to create an index with the ONLINE clause:

DROP INDEX xyz;

ORA-08104: this index object 983257 is being online built or rebuilt

then you might need to call function DBMS_REPAIR.ONLINE_INDEX_CLEAN to be able to retry the DROP INDEX statement, however there are two caveats:

1. DBMS_REPAIR is not granted by default to all users, so you probably need to ask the DBA for the grant execute first;
2. When I tried the operation the DBA granted temporarily DBA role to my user, just in case, so I don't know if this is really necessary as the documentation doesn't mention it. 

The first parameter of the function call is the object ID mentioned in the error message.
The second optional parameter wait_for_lock (missing in the code below) enables a retry mechanism in case the underlying table is locked for a short time by some other sessions.

set serveroutput on
DECLARE
  b boolean;
BEGIN 
  b := sys.dbms_repair.online_index_clean(983257);
  if b then
    dbms_output.put_line('cleanup successful');
  else
    dbms_output.put_line('cleanup failed');
  end if;
END;
/

In my case after running the function, I could drop the index successfully.