René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

create context [Oracle]

create context context-namespace using package-name;
create context context-namespace using package-name initialized externally;
create context context-namespace using package-name initialized globally;
create context context-namespace using package-name accessed globally;
create context creates a context namespace. Such a context namespace is a container, if you will, for attribute value pairs. The value of an attribute can the be queried with sys_context('context-namespace', 'attribute-name').
A value for an attribute is set using dbms_session.set_context('context-namespace', 'attribute-value', value). For security reasons, Oracle only allows to execute dbms_session.set_context from within the package package-name

Simple examples

«normal» context

conn / as sysdba

create user rene 
identified by rene 
default tablespace users 
temporary tablespace temp;

grant create session,
      create procedure,
      create any context
      to rene;

conn rene/rene

create context some_context using some_package;

create package some_package as

    procedure set_value_in_context(some_value in varchar2);

end some_package;
/

create package body some_package as

    procedure set_value_in_context(some_value in varchar2) is
    begin
        dbms_session.set_context('some_context', 'some_attribute', some_value);
    end set_value_in_context;

end some_package;
/

exec some_package.set_value_in_context('foo');

select sys_context('some_context', 'some_attribute') from dual;
SYS_CONTEXT('SOME_CONTEXT','SOME_ATTRIBUTE')
----------------------------------------------------------
foo
A «normal» context is not valid accross sessions:
conn rene/rene;
select sys_context('some_context', 'some_attribute') from dual;
This time, sys_context returns null since I reconnected.

accessed globally

conn rene/rene

create context global_context using global_package accessed globally;

create package global_package as

    procedure set_value_in_context(global_value in varchar2);

end global_package;
/

create package body global_package as

    procedure set_value_in_context(global_value in varchar2) is
    begin
        dbms_session.set_context('global_context', 'global_attribute', global_value);
    end set_value_in_context;

end global_package;
/

exec global_package.set_value_in_context('valid accross sessions');
I've set the context in a session and will connect again (thus starting a new session):
conn rene/rene

select sys_context('global_context', 'global_attribute') from dual;
SYS_CONTEXT('GLOBAL_CONTEXT','GLOBAL_ATTRIBUTE')
--------------------------------------------------------------------------------
valid accross sessions
The context is accessed globally, so the value of the context/attribute tuple is visible across sessions.

Misc

Contexts live in the SYS schema. This has two implications:
  • A context cannot be created like create context some_schema.some_context
  • The required system privilege is create ANY context. There is no create context system privilege.

Links