[ Oracle PL/SQL ] セッションを使って SQL で値を共有する ( DBMS_SESSION )

WEB アプリケーションのように、Oracle でもセッションを使用して SQLで値を共有することができます。ここでは、dbms_session を使用したサンプルコードを掲載しています。

セッションを操作するパッケージの作成

dbms_session を使用して SQL で値を共有すには dbms_session.set_context プロシージャを使用しますが、通常の SQL からは実行できません。ここではパッケージを作成して、その中から呼び出しています。(パッケージでなくともプロシージャでも OK です)

-- コンテキストを管理するパッケージ
CREATE OR REPLACE PACKAGE context_pkg AS
  /*
   * コンテキストを設定する
   */
  PROCEDURE set_context(p_name   IN  VARCHAR2,
                        p_value  IN  VARCHAR2);
  /*
   * 指定したコンテキストをクリアする
   */
  PROCEDURE clear_context (p_name  IN  VARCHAR2);

  /*
   * コンテキストをすべてクリアする
   */
  PROCEDURE clear_all_context;

  /*
   * コンテキストの一覧を確認する
   */
  PROCEDURE show_context;
END;

-- パッケージの実装部
CREATE OR REPLACE PACKAGE BODY context_pkg IS
  
  -- ネームスペース名
  APP_NAMESPACE constant varchar2 := 'namespace_ctx';

  PROCEDURE set_context(p_name  IN VARCHAR2,
                        p_value IN VARCHAR2) IS
  BEGIN
    -- 登録するネームスペースは create context文で作成したもの
    dbms_session.set_context(APP_NAMESPACE, p_name, p_value);
  END;

  PROCEDURE clear_context (p_name IN VARCHAR2) IS
  BEGIN
    dbms_session.clear_context(APP_NAMESPACE, attribute => p_name);
  END;

  PROCEDURE clear_all_context IS
  BEGIN
    dbms_session.clear_all_context(APP_NAMESPACE);
  END;

  PROCEDURE show_context IS
    list_size number;
    context_list dbms_session.AppCtxTabTyp;
  BEGIN
    -- 現在有効なコンテキストの一覧を出力する
    dbms_session.list_context(context_list, list_size);
    for i in 1..list_size loop
      dbms_output.put_line(
        context_list(i).namespace || '.' ||
        context_list(i).attribute || '=' ||
        context_list(i).value
      );
    end loop;
  END;

END;

コンテキストの作成 ( create context )

create context文でコンテキストを操作するパッケージとコンテキスト ( ネームスペース ) を関連付けておく必要があります。

CREATE OR REPLACE CONTEXT namespace_ctx USING context_pkg

関連付けを行っていない場合、以下の Oracle エラーが発生します。

ORA-01031 : 権限が不足しています。
ORA-06512 : sys.dbms_session

また、コンテキストを作成するユーザに権限が不足している場合は、権限を付与する必要もあります。

GRANT CREATE ANY CONTEXT TO <ユーザー>

動作確認

上記のパッケージの動作の確認結果は以下のようになります。

/*
 * セッションにコンテキストをセットする
 */
call context_pkg.set_context('name1', 'value1')
call context_pkg.set_context('name2', 'value2')

/*
 * コンテキスト情報の読み出し
 */
SELECT sys_context('namespace_ctx', 'name1') AS name1,
       sys_context('namespace_ctx', 'name2') AS name2
FROM   dual;

name1      name2
-----------------------------------
value1     value2

/*
 * コンテキストをクリア
 */
call context_pkg.clear_context('name2');

SELECT SYS_CONTEXT('namespace_ctx', 'name1') AS name1,
       SYS_CONTEXT('namespace_ctx', 'name2') AS name2
FROM   dual;

name1      name2
-----------------------------------
value1

参考

• アプリケーション・コンテキストを使用したユーザー情報の取得