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;
/