Session variable and Context in Oracle, Using dbms_session.set_context

Oracle  'Context'  can be used to capture session variables

• A context is associated with a package and in that package's procedure we can use this context


• It holds attribute value pair.


• Attribute value is set using DBMS_SESSION.SET_CONTEXT


• Attribute value can be set inside that package only to which it is associated.


• Attribute value can be queried with 'sys_context' function.

Let's test it

Step 1:

For creating and using context we would need below privileges

GRANT CREATE SESSION, CREATE ANY context TO USER; 

Step 2:

create a context 'c_userAttr' associated with package 'p_userAttr'

Syntax
create context context-namespace using package-name;

CREATE context c_userattr USING p_userattr; 

Step 3:
Now we will create package definition

CREATE OR replace PACKAGE p_userattr
IS
PROCEDURE set_contexts;
END; 

Write package body that sets the attribute for context

We can set as many attributes we want using DBMS_SESSION.SET_CONTEXT statement
In this case 'username' is the attribute we will set here

CREATE OR replace PACKAGE BODY p_userattr
IS
c_context CONSTANT VARCHAR2(30) := 'c_userAttr';
PROCEDURE Set_contexts(v_uname IN VARCHAR2)
IS
BEGIN
dbms_session.Set_context (c_context, 'username', v_uname);
END;
END; 

Last Step

In application we can set context with procedure call like this

BEGIN
p_userattr.Set_contexts('crazy');
END; 
and in the same session we can query value in that context namespace like

SELECT Sys_context('C_USERATTR', 'USERNAME')
FROM   dual;