Table of Contents

About

An application context is a set of (name-value|variable-value) pairs that Oracle Database stores in memory during a session.

Oracle provides a built-in namespace called USERENV, which describes the current session parameter.

This context are managed with the following commandos:

Privileges

As Sysdba: 

-- SYSTEM PRIVILEGES
GRANT CREATE ANY CONTEXT TO "HR" ;

Steps

Creation of the Contexts

As HR: 

CREATE OR REPLACE CONTEXT empno_ctx USING set_context_variable_package;

CREATE OR REPLACE CONTEXT myContext USING set_context_variable_package;

A context must be use within a package otherwise you will get an ORA-01031: insufficient privileges.

Creation of the package

As HR: 

CREATE OR REPLACE
PACKAGE set_context_variable_package
IS

PROCEDURE set_empno;

PROCEDURE set_variable_value(
    n VARCHAR2,
    v VARCHAR2);

END;
/

CREATE OR REPLACE
PACKAGE BODY set_context_variable_package
IS
  -- dbms_session.set_context
  -- can only be called within the package to which it belongs
  -- If you try to execute DBMS_SESSION.SET_CONTEXT you'll get an error, as
  -- shown here:
  -- ORA-01031: insufficient privileges

PROCEDURE set_empno
IS
  emp_id NUMBER;
BEGIN
  
  SELECT
    EMPLOYEE_ID
  INTO
    emp_id
  FROM
    HR.EMPLOYEES
  WHERE
    email = SYS_CONTEXT('USERENV', 'SESSION_USER');
  
  DBMS_SESSION.SET_CONTEXT('empno_ctx', 'employee_id', emp_id);

EXCEPTION

WHEN NO_DATA_FOUND THEN
  NULL;

END;

PROCEDURE set_variable_value(
    n VARCHAR2,
    v VARCHAR2)
AS
BEGIN
  dbms_session.set_context('myContext',n,v);
END;

END;
/

Call in the Context empno_ctx

As HR: 

SELECT
    SYS_CONTEXT('USERENV', 'SESSION_USER')
  FROM
    dual;

Ouptut: 

SYS_CONTEXT('USERENV','SESSION_USER')                                                                        
---------------------------------------
HR


                       
  SELECT
    SYS_CONTEXT('empno_ctx', 'employee_id')
  FROM
    dual;

Ouptut: 

SYS_CONTEXT('empno_ctx', 'employee_id')
---------------------------------------
1

Call in the Context myContext

As HR: 

 
EXEC set_context_variable_package.set_variable_value('myVar','myValue');

SELECT
  SYS_CONTEXT('myContext', 'myVar')
FROM
  dual;

Ouptut: 

SYS_CONTEXT('MYCONTEXT','MYVAR') 
-------------------------------------- 
myValue

Documentation / Reference