sys_context not working from APEX application / environment

731104

I am struggling to retrieve a user-defined context value using sys_context(...) from APEX application / environment

I have created a global context to be used as a storage mechanism for log severity to be recorded.

 CREATE OR REPLACE CONTEXT PA_LOGGER_CTX USING PA_LOGGER ACCESSED GLOBALLY;

It is seeded through a function pa_logger.set_severity(1) within the package pa_logger through (excerpt):

  dbms_session.set_context(c_ctx_context_label, c_ctx_severity_label, TO_CHAR(p_severity_in));

  with:
  c_ctx_context_label     CONSTANT NVARCHAR2(16) := 'PA_LOGGER_CTX';
  c_ctx_severity_label    CONSTANT NVARCHAR2(8) := 'SEVERITY';
  p_severity_in   IN  PLS_INTEGER

using the following from sqlplus, other DB-tools, works as expected:

-- setting it
BEGIN
  pa_logger.set_severity(2);
END;

-- displaying it
SELECT sys_context('PA_LOGGER_CTX', 'SEVERITY') FROM DUAL;

returns 2

This works from different sessions, different users ... no problem.

Using it from within an APEX (accessed via embedded gateway/XE11.2 or OHS/11.2.0.2.0 ) application or even from the "SQL Commands" from the APEX Workspace returns a NULL value for :

SELECT sys_context('PA_LOGGER_CTX', 'SEVERITY') FROM DUAL;

And

   SELECT value  FROM GLOBAL_CONTEXT WHERE NAMESPACE='PA_LOGGER_CTX' and ATTRIBUTE = 'SEVERITY';

returns the right value, which obviously defies the purpose of using the CONTEXT.
It is as well possible to set the CONTEXT to a different value, which is picked up outside APEX, but even so using sys_context does not work, always returning NULL

As a note aside: using sys_context with a standard CONTEXT, e.g. SELECT sys_context('USERENV', 'NLS_DATE_FORMAT') from DUAL works without any problems.


I guess I have a conceptual break somewhere. Does this have to do with the APEX user used, wrapping within the APEX environment, the way the context is defined ....

Any help is appreciated.

Many thanks,
- Thomas

Roel Hartman

Just a quick shot: As APEX uses session pooling the setting of the context might be in another session than retrieving the context. So when you using it on a page, try to set the context in a PL/SQL process after header and retrieve it "later" (for instance in a PL/SQL region).

Rod West

Hi,

I think you misunderstand how the application context works. If you call DBMS_SESSION.SET_CONTEXT and do not specify a client_id in the parameters then the context is set for a session with a NULL client identifier. If a session then uses DBMS_SESSION.SET_IDENTIFIER to set an client identifier (like Apex sessions do) then the context will no longer be available to the session.

Therefore you will have to call DBMS_SESSION.SET_CONTEXT when Apex user logs on and set the application context for the Apex session specifying the client_identifier in the call to DBMS_SESSION.SET_CONTEXT. The context will be then available for the length of that session.

If you want a true global variable available accross the database to all sessions then store the value in a table.

Rod West

731104

Hi Rod,

Thanks for the time to answer. Meanwhile I found some more hints on the subject (996119 and I do now understand the concept.

I understand that I can even clear the specific CLIENT_IDENTIFIER by issuing a "dbms_session.clear_identifier()", which then subsequently works

Looking at the CLIENT_IDENTIFIER as set by APEX it is "<apex_login_user><apex_session>";
I understand it is set for every DISPLAY-SUBMIT it is reset by the APEX framework.

Hence it is not sufficient to set it for a user session; it needs to be changed for every display-submit. I can achieve this through application processes, but I am really reluctant to do so, because I do not know, if APEX relies on the CLIENT_IDENTIFIER to be set the way it is done. I suspect a purpose behind this, causing me problems elsewhere. I post this as a seprate question as this question has been answered.

On to your point about: Using a table variable. This is the way it works currently, but allegedly using the CONTEXT would give us for the logging configuration a performance gain of about 1 dimension..... but unfortunately other problems..

Thanks!
- Thomas

Rod West

Hi,

Hence it is not sufficient to set it for a user session; it needs to be changed for every display-submit.

I don't think so. The CLIENT_IDENTIFIER as set to <apex_login_user> : <apex_session> and therefore will remain the same until the user logs out.

Therefore if you set the context using

dbms_session.set_context(namespace=><namespace>,
                         attribute=><attribute>, 
                         value=><value>,
                         username=>USER, 
                         client_id=>SYS_CONTEXT ('USERENV', 'CLIENT_IDENTIFIER'))

after the user logs on then the context will be set until the user logs out.

Rod West