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 :
- save the current CLIENT_IDENTIFIER in a variable
- clear it using DBMS_SESSION.CLEAR_IDENTIFIER
- assign the desired value retrieved from the global context to another variable
- restore the original CLIENT_IDENTIFIER using DBMS_SESSION.SET_IDENTIFIER
- return the desired value.
2 comments:
You may find this documentation helpful - https://docs.oracle.com/en/database/oracle/apex/24.1/htmdb/correlating-apex-sessions-to-database-sessions.html
Thank you for your comment Jeffrey, as I pointed out in the last part of my posting the CLIENT_IDENTIFIER should be preserved in order to avoid side effects.
If one reads carefully the documentation I mentioned in the posting it's not so straightforward to understand that you won't get what you expect if the CLIENT_IDENTIFIER has been set beforehand, in this case by APEX itself, and that will affect the results in your code unless you clear it (at least temporarily).
The CLIENT_IDENTIFIER is of great help when looking at what an active APEX/Oracle session is doing, that's why I thing it's wise to preserve its state in case you need to read a globally accessed context (which I don't really expect to be a problem affecting many users), but it's something to be aware of in that case, it's easy to be puzzled when you get different results depending on whether you execute a procedure from within an APEX session or outside of it.
Post a Comment