Oracle Database Cross Session Data Cache – Introducing the Globally Accessible Database Context

2
Share this on .. Tweet about this on Twitter0Share on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on TumblrBuffer this page

 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.

Share this on .. Tweet about this on Twitter0Share on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on TumblrBuffer this page

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

2 Comments