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

Lucas Jellema 2
0 0
Read Time:2 Minute, 5 Second

 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.

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

2 thoughts on “Oracle Database Cross Session Data Cache – Introducing the Globally Accessible Database Context

Comments are closed.

Next Post

Reverting tagged version back to trunk in subversion using Eclipse (Subclipse)

Yesterday I was given a new assignment. Of a certain system all development was stopped a month ago. The latest version, deployed and tested on the test environment, will not continue to be deployed on the production environment. The current version running on production is almost a year old. All […]
%d bloggers like this: