Hi Tom!
Running on Oracle 11g (11.2.0.4.0) I try to use a global context to share parameters between user sessions.
Like the docs says:
"username set to a value - client_id set to NULL" means:
This combination enables an application context to be accessed by multiple sessions, as long as the username setting is the same throughout.
With this knowledge I try this as the dba user MG:
create or replace context my_ctx using my_pkg accessed globally;
-- + --
create OR replace package my_pkg is
procedure set_parameter ( p_parameter in varchar2, p_value in varchar2 );
function get_parameter ( f_parameter in varchar2 ) return varchar2;
end;
/
-- + --
create OR replace package body my_pkg is
pk_ctx constant varchar2(30) := 'my_ctx';
procedure set_parameter ( p_parameter in varchar2, p_value in varchar2 ) is
begin
dbms_session.set_context ( namespace => pk_ctx, attribute => p_parameter, value => p_value, username => USER );
end set_parameter;
-- + --
function get_parameter ( f_parameter in varchar2 ) return varchar2 is
begin
return ( sys_context ( pk_ctx, f_parameter ) );
end get_parameter;
-- + --
end my_pkg;
/
-- + --
As user MG (the context-admin and package owner), I execute:
execute mg.my_pkg.set_parameter ( 'Ask-Tom', 'MG says: always' );
Now looking into global_context with
select attribute, value, username
from global_context
where namespace = 'MY_CTX'
/
shows
ATTRIBUTE VALUE USERNAME
-------------------- ------------ ----------
ASK-TOM always MG
Okay.
With another SQL*Plus session as user "TOM" I execute the set_parameter package procedure:
execute mg.my_pkg.set_parameter ( 'Ask-Tom', 'Tom says: what else' );
Back in the first SQL*Plus session as the package owner MG the query
select attribute, value, username
from global_context
where namespace = 'MY_CTX'
/
now shows
ATTRIBUTE VALUE USERNAME
-------------------- ------------ ----------
ASK-TOM what else TOM
Why the data from the user MG gets overwritten?
I would expect that there is another entry in the global context where the "owner" is TOM.
Regards and thanks in advance!
There is a global application context per
client identifier. Not per username!
When you call set_parameter for a different user, you've transferred ownership of that key to the other user. So only they can access it via sys_context/get_parameter.
So when you connect back as MG, using these methods to access the context returns null. Setting it again as MG switches ownership back to MG. So all MG's session can see the value. But none of TOM's sessions can.
If you want:
* Each database user to have their own values for a given key across
all their sessions* To use the
same key for different users that connect at the same time
you need to set the client identifier to the same value. Such as their username.
create OR replace package my_pkg is
procedure set_parameter (
p_parameter in varchar2,
p_value in varchar2
);
function get_parameter ( f_parameter in varchar2 ) return varchar2;
end;
/
-- + --
create or replace package body my_pkg is
pk_ctx constant varchar2 (30) := 'MY_CTX';
g_user constant varchar2 (30) := user;
procedure set_parameter (
p_parameter in varchar2,
p_value in varchar2
) is
begin
dbms_session.set_identifier( g_user );
dbms_session.set_context (
namespace => pk_ctx,attribute => p_parameter,
value => p_value, username => g_user,
client_id => g_user
);
end set_parameter;
-- + --
function get_parameter (
f_parameter in varchar2
) return varchar2 is
begin
dbms_session.set_identifier( g_user );
return (sys_context ( pk_ctx,f_parameter));
end get_parameter;
-- + --
end my_pkg;
/
grant create session to u
identified by u;
grant execute on my_pkg to u;
execute my_pkg.set_parameter ( 'ASK-TOM', 'MG says: always' );
select *
from global_context
where namespace = 'MY_CTX'
/
NAMESPACE ATTRIBUTE VALUE USERNAME CLIENT_IDENTIFIER
MY_CTX ASK-TOM MG says: always CHRIS CHRIS
select chris.my_pkg.get_parameter ( 'ASK-TOM' ) from dual;
CHRIS.MY_PKG.GET_PARAMETER('ASK-TOM')
MG says: always
conn u/u
select chris.my_pkg.get_parameter ( 'ASK-TOM' ) from dual;
CHRIS.MY_PKG.GET_PARAMETER('ASK-TOM')
<null>
execute chris.my_pkg.set_parameter ( 'ASK-TOM', 'Chris says: per client id' );
select chris.my_pkg.get_parameter ( 'ASK-TOM' ) from dual;
CHRIS.MY_PKG.GET_PARAMETER('ASK-TOM')
Chris says: per client id
select *
from global_context
where namespace = 'MY_CTX'
/
NAMESPACE ATTRIBUTE VALUE USERNAME CLIENT_IDENTIFIER
MY_CTX ASK-TOM Chris says: per client id U U
conn chris/chris
select chris.my_pkg.get_parameter ( 'ASK-TOM' ) from dual;
CHRIS.MY_PKG.GET_PARAMETER('ASK-TOM')
MG says: always
select *
from global_context
where namespace = 'MY_CTX'
/
NAMESPACE ATTRIBUTE VALUE USERNAME CLIENT_IDENTIFIER
MY_CTX ASK-TOM MG says: always CHRIS CHRIS