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

See On parametrizing views with contexts.


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