Working with sys_context

How to create Oracle SYS_CONTEXT ?

Oracle SYS_CONTEXT holds the value of the parameter associated with context namespace.

Prerequisite  

Database user needs grant to create context. 

GRANT CREATE ANY CONTEXT TO c##ontoor_dev;

Package to set parameter value.

CREATE OR REPLACE PACKAGE sys_context_utility
IS
   /*
                      Version 1.0.1       11- March- 2020
                       */
   PROCEDURE set_context (p_context_name    IN VARCHAR2,
                          p_context_value   IN VARCHAR2);
END sys_context_utility;


CREATE OR REPLACE PACKAGE BODY sys_context_utility
IS
   PROCEDURE set_context (p_context_name    IN VARCHAR2,
                          p_context_value   IN VARCHAR2)
   IS
   BEGIN
      DBMS_SESSION.set_context ('MY_CONTEXT',
                                p_context_name,
                                p_context_value);
   END set_context;
END sys_context_utility;

Create a context using the package  sys_context_utility.

CREATE CONTEXT MY_CONTEXT USING sys_context_utility;

Set parameter value

BEGIN
   sys_context_utility.set_context ('FNAME', 'ASHISH');
   sys_context_utility.set_context ('LNAME', 'Sahay');
   sys_context_utility.set_context ('WEBSITE', 'ashishsahay.com');
END;

After all the values are set, try to fetch.

SELECT SYS_CONTEXT ('MY_CONTEXT', 'FNAME'),
       SYS_CONTEXT ('MY_CONTEXT', 'LNAME'),
       SYS_CONTEXT ('MY_CONTEXT', 'WEBSITE')
  FROM DUAL;

Drop commands

DROP CONTEXT MY_CONTEXT;
/
DROP PACKAGE sys_context_utility;
/
revoke  CREATE ANY CONTEXT from  c##ontoor_dev;
/