Stumblings

A quick writeup on Oracle Application Contexts

Overview
Link to 9i Documentation
Link to 10g Documentation

From the documentation:

Application contexts allow you to write applications which draw upon certain aspects of a user’s session information. It provides a way to define, set, and access attributes that an application can use.
An Application context is a namespace. Each Application Context namespace can have multiple attributes (name/value pairs). These are associated with a user session and can be set/accessed multiple times.

Creating Context
To create a context namespace called sls_app_context:


CREATE CONTEXT sls_app_context USING sls_app_context_pkg
/


To create a context namespace, you must have CREATE ANY CONTEXT system privilege.

sls_app_context_pkg is a PL/SQL package that can change the Application context.

You can only set the context attributes inside the trusted procedure/package you named in the CREATE CONTEXT statement. This prevents a malicious user from changing context attributes without proper attribute validation. The sls_app_context_pkg is the trusted package.

Set Context
Package sls_app_context_pkg would be defined as follows:
CREATE OR REPLACE PACKAGE sls_app_context_pkg AS
-- Set App Id for App User management
PROCEDURE set_app_id (p_app_id Varchar2);
-- Set period of interest
PROCEDURE set_period (p_period Integer);
END;
/
CREATE OR REPLACE PACKAGE BODY sls_app_context_pkg AS
-- Set App Id for App User management
PROCEDURE set_app_id (p_app_id Varchar2) IS
BEGIN
DBMS_SESSION.SET_CONTEXT('sls_app_context', 'app_id', p_app_id);
END;
-- Set period of interest
PROCEDURE set_period (p_period Integer) IS
BEGIN
DBMS_SESSION.SET_CONTEXT('sls_app_context', 'period', p_period);
END;
END;
/

Now, the context can be set by any program unit (procedure/trigger etc) by calling the appropriate procedure from sls_app_context_pkg.

PROCEDURE login_proc IS
v_app_id Varchar2(256);
BEGIN
-- Set app_id context attribute
SELECT char_value
INTO v_app_id
FROM tsysstg stg
WHERE stg.code = 'APP_ID';
sls_app_context_pkg.set_app_id(v_app_id);
-- set period context attribute
sls_app_context_pkg.set_period(To_Number(To_Char(Sysdate,'MM'))); -- Assuming period is month number
END;
/

Get Context
To access the context attribute value use the SYS_CONTEXT function:

DECLARE
v_period Integer;
BEGIN
...
...
v_period := SYS_CONTEXT('SLS_APP_CONTEXT','period');
...
END;

This function can be used in various places. Some examples:
1. In queries (including views)
2. In PL/SQL code
3. As default values for table columns

CREATE TABLE txxxtab (
id number(10),
col1 varchar2(30),
col2 varchar2(10),
period number(10) DEFAULT SYS_CONTEXT('SLS_APP_CONTEXT','period'))
/
CREATE VIEW txxxtab_vw AS
SELECT id,
col1,
col2
FROM txxxtab
WHERE period = SYS_CONTEXT('SLS_APP_CONTEXT','period')
/
CREATE OR REPLACE PROCEDURE some_proc IS
v_period Integer;
BEGIN
...
...
v_period := SYS_CONTEXT('SLS_APP_CONTEXT','period');
...
END;
/
insert into txxxtab_vw values (1,'AAA','A');
insert into txxxtab_vw values (1,'BBB','B');
insert into txxxtab_vw values (1,'BBB','B');
SELECT *
FROM txxxtab
WHERE period = SYS_CONTEXT('SLS_APP_CONTEXT','period')
/

Built-in Contexts
There is a built-in context USERENV that provides information about the current session. Some examples:

TERMINAL
Returns the operating system identifier for the client of the current session. “Virtual” in TCP/IP

LANGUAGE
Returns the language and territory currently used by the session, along with the database character set in the form: language_territory.characterset

LANG
Returns abbreviation for the language name

SESSIONID
Returns auditing session identifier

INSTANCE
Returns instance identification number of the current instance

OS_USER
Returns the operating system username of the client process that initiated the database session

These are accessed like user defined Application contexts i.e. using the SYS_CONTEXT function.

DECLARE
v_os_user varchar2(256);
BEGIN
...
...
v_os_user := SYS_CONTEXT('USERENV','OS_USER');
...
END;
/