0 
0 An Application Context in the Oracle Database is a name in-memory collection of key-value pairs. Applications can store values in an Application Context – using a package associated with the context – and retrieve values from them using sys_context( name of context, key for value). Sys_context can be used in PL/SQL as well as in SQL. Note that references to context values using sys_context are treated as bind-variables by the SQL engine – and evaluated only once at the start of the query.
An application context is usually associated with a session, its data stored in the UGA and the values only accessible within the session itself. There is however a second type of application context, that is accessible from all sessions in the database instance. The data in such a global application context lives in the SGA and survives the end of the session that stored the data in the context – as well as other session until the database shuts down.
This type of context can be used to exchange and share data between sessions. In particular it can be used as a global cache for global settings and values that are used frequently but may be somewhat expensive to retrieve. This article shows how to create a globally accessible application context and how to use it.
First create the PL/SQL package that will control the context of the application context, for example:
create or replace package cache_mgr as procedure put_in_cache ( p_key in varchar2 , p_value in varchar2 ); end;
Then create the Application Context itself; specify ACCESSED GLOBALLY to make it a global application context:
Now is a good time to flesh out the package body as well:
create or replace package body cache_mgr as procedure put_in_cache ( p_key in varchar2 , p_value in varchar2 ) is begin DBMS_SESSION.SET_CONTEXT( 'global_cache',p_key ,p_value); end put_in_cache; end;
Store a value in the global cache, effectively publishing it to all sessions in the database:
You can retrieve the value in the normal way using sys_context in the same session:
And verify that the value is indeed part of the Global Context Data Space:
If we connect anew as the samne or as another user – starting a new session – we can access the joke of the day from the global_cache application context:
Resources
The SQL script that goes with these examples: globallyaccessiblecontext.txt.
Related posts:
Tag Cloud analysis in SQL and PL/SQL – Using Collections, Table Operator, Multiset and Collect operator Oracle SQL and PL/SQL: juggling with user defined types (ADT) and XMLType – for creating an ADT based XML Service API 
2 dagen seminar door Steven Feuerstein: Best of Oracle PL/SQL (8 en 9 december) OOW 2012: The Very Very Latest in Database Development (CON4792) The Very Very Latest in Database Development – slides from the Expertezed presentation
This post gave us a major Brainstorm session of all the possibilities we can utilize on our blog.
Cool. Have used contexts loads, never noticed the ACCESSED GLOBALLY option! RTFM – all of it, more than once…