I need a sys_context which can be accessed globally across the instance.
And this does just that:
dbms_session.set_context( namespace => 'my_ctx', attribute => 'key', value => theKey)
Every session can reterive the value with:
sys_context('my_ctx', 'key')
The problems comes after this:
dbms_session.set_identifier( sessionId );
Once this is issued, then getting the context stops working:
sys_context('my_ctx', 'key')
I understand why and can overcome it by resetting the context with the sessionId:
dbms_session.set_context ( namespace => 'my_ctx', attribute => 'key', value => theKey, client_id => sessionId)
Problem is I have some variables which are common to all and I’d rather not need to reset each one after set_identifier() is issued.
Is there a way to grab the key value which is not associated with a sessionId?
I've tried creating a 2nd sys_context for these instance wide globals and it behaves the same way.
I'd like to solve this as a sys_context and not as global procedure variable. I'd like something which will not switch contexts when being used in a sql statement. Do I have to compromise and use a global temp table? The key value will not change...I have not tired a deterministic function or an authId function.....hummm, authId might work...wonder if that would cause a context switch? I'll try it now.