Oracle context #oracle #sql

Oracle context.md

-- User
connect / as sysdba
drop user tst_ctx cascade;
create user tst_ctx identified by <password>;
grant connect, resource to tst_ctx;
grant create any context to tst_ctx;
alter user tst_ctx default role all;
connect tst_ctx/<password>

CREATE OR REPLACE CONTEXT Session_ctx USING pkgContext;

-- Trusted PKG
create or replace package pkgContext as
  procedure set_parameter(p_name   in  varchar2,
                          p_value  in  varchar2);
end pkgContext;
/                          

create or replace package body pkgContext is
  procedure set_parameter (p_name   in  varchar2,
                           p_value  in  varchar2) is
  begin
    sys.dbms_session.set_context('Session_ctx', p_name, p_value);
  end set_parameter;
end pkgContext;  
/

-- Set context
exec pkgContext.set_parameter('Key', 'The quick brown fox jumps over the lazy dog');

-- Get context
select sys_context('Session_ctx', 'Key') "Key"
from   dual;