{アプリケーションコンテキスト}グローバルアプリケーションコンテキストを作成

 

https://docs.oracle.com/cd/F19136_01/dbseg/using-application-contexts-to-retrieve-user-information.html#GUID-BF276A5E-BC14-4B49-A02A-2EC984325805

--手順1: ユーザー・アカウントの作成
GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE TO sysadmin_ctx IDENTIFIED BY oracle;

GRANT EXECUTE ON DBMS_SESSION TO sysadmin_ctx;

GRANT CREATE SESSION TO apps_user IDENTIFIED BY oracle;

--手順2: グローバル・アプリケーション・コンテキストの作成
conn sysadmin_ctx/oracle

CREATE CONTEXT global_cust_ctx USING cust_ctx_pkg ACCESSED GLOBALLY;

--手順3: グローバル・アプリケーション・コンテキストのパッケージの作成

CREATE OR REPLACE PACKAGE cust_ctx_pkg
  AS
   PROCEDURE set_session_id(session_id_p IN NUMBER); 
   PROCEDURE set_cust_ctx(sec_level_attr IN VARCHAR2, 
     sec_level_val IN VARCHAR2);
   PROCEDURE clear_hr_session(session_id_p IN NUMBER);
   PROCEDURE clear_hr_context;
  END;
 /

CREATE OR REPLACE PACKAGE BODY cust_ctx_pkg
  AS
  session_id_global NUMBER;
 
 PROCEDURE set_session_id(session_id_p IN NUMBER) 
  AS
  BEGIN
   session_id_global := session_id_p;
   DBMS_SESSION.SET_IDENTIFIER(session_id_p);
 END set_session_id;
 
 PROCEDURE set_cust_ctx(sec_level_attr IN VARCHAR2, sec_level_val IN VARCHAR2)
  AS
  BEGIN
   DBMS_SESSION.SET_CONTEXT(
    namespace  => 'global_cust_ctx',
    attribute  => sec_level_attr,
    value      => sec_level_val,
    username   => USER, -- Retrieves the session user, in this case, apps_user
    client_id  => session_id_global);
  END set_cust_ctx;
 
  PROCEDURE clear_hr_session(session_id_p IN NUMBER)
   AS
   BEGIN
     DBMS_SESSION.SET_IDENTIFIER(session_id_p);
     DBMS_SESSION.CLEAR_IDENTIFIER;
   END clear_hr_session;

 PROCEDURE clear_hr_context
  AS
  BEGIN
   DBMS_SESSION.CLEAR_CONTEXT('global_cust_ctx', session_id_global);
  END clear_hr_context;
 END;
/

GRANT EXECUTE ON cust_ctx_pkg TO apps_user;

--手順4: 新規作成したグローバル・アプリケーション・コンテキストのテスト

conn apps_user/oracle

EXEC sysadmin_ctx.cust_ctx_pkg.set_session_id(34256);
SELECT SYS_CONTEXT('userenv', 'client_identifier') FROM DUAL;

EXEC sysadmin_ctx.cust_ctx_pkg.set_cust_ctx('Category', 'Gold Partner');
EXEC sysadmin_ctx.cust_ctx_pkg.set_cust_ctx('Benefit Level', 'Highest');

col category format a13
col benefit_level format a14

SELECT SYS_CONTEXT('global_cust_ctx', 'Category') category, SYS_CONTEXT('global_cust_ctx', 'Benefit Level') benefit_level FROM dual;