Working With SYS_CONTEXT

March 11, 2020Posted inORACLE

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.

Copy Code
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

Copy Code
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.

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


 Drop commands

Copy Code
DROP CONTEXT MY_CONTEXT;
/
DROP PACKAGE sys_context_utility;
/
revoke  CREATE ANY CONTEXT from  c##ontoor_dev;
/
Tags: