2022年8月22日月曜日

パラメータを受け取るレポートのソース設定

 レポートのソースとなる表/ビューまたはSQL問合せに、ページ・アイテムの値をパラメータとして適用する実装方法はいくつかあります。最近では、Oracle Database 19cにバックポートされた新機能SQLマクロも活用できます。

従業員名を選択するページ・アイテムを作成し、選択した従業員と同じ部門に所属する従業員をレポートに一覧します。

以下の実装方法を試してみます。

  1. SQL問合せにバインド変数を使う
  2. V関数を使ったビューにする
  3. SQL問合せを返すファンクション本体を使う
  4. SQLマクロを使う
  5. アプリケーション・アイテムを使う
  6. アプリケーション・コンテキストを使う
どの実装方法でも、以下の動作をするページになります。



準備


テスト用のデータとして、サンプル・データセットのEMP/DEPTに含まれる表EMPを使用します。

SQLワークショップのユーティリティのサンプル・データセットより、EMP/DEPTのデータセットをインストールします。言語は英語でも日本語でも構いません。今回の例では英語を選択しています。アプリケーションの作成は行いません。


アプリケーション作成ウィザードを起動します。

アプリケーションの名前をパラメータ付きレポートとします。ホーム・ページの編集を開いて、削除します。代わりにページを作成するため、ページの追加をクリックします。


追加するページはクラシック・レポートですが、対話モード・レポートを選択します（追加ページを開いて、クラシック・レポートを選ぶこともできます）。


ページ名はバインド変数とします。アプリケーションの作成後に、バインド変数を使ったレポートのソースを実装します。表またはビュー、クラシック・レポートを選択します。表またはビューとしてEMPを選択します。データの編集は行わないため、フォームを含めるにチェックは入れません。

ルックアップ列を開き、ルックアップ・キー１としてMGR、表示列１としてEMP.ENAMEを指定します。次にルックアップ・キー２としてDEPTNO、表示列２としてDEPT.DNAMEを指定します。作成されたレポートの列MGRに従業員名であるENAMEの値、列DEPTNOに部門名である列DNAMEの値が数値の代わりに表示されます。

以上の設定を行い、ページの追加をクリックします。


アプリケーションの作成を実行します。


アプリケーションが作成されました。


最初に最も一般的な方法である、バインド変数を使った実装をページ番号１のホーム・ページに行います。その他の実装は、作成したページ番号１をコピーして新たなページを作成し、そのページを改変します。


SQL問合せにバインド変数を使う



リージョンEmployeesに従業員を選択するページ・アイテムP1_ENAMEを作成します。

識別の名前としてP1_ENAME、タイプとして選択リストを選びます。ラベルはEnameとします。LOVのタイプとしてSQL問合せを選択し、SQL問合せとして以下を記述します。

select ename d, ename r from emp

追加値の表示はOFF、NULL値の表示はONとし、NULL表示値として-- 従業員を選択--と記述します。


ページ・アイテムP1_ENAMEの値を変更した際にレポートをリフレッシュするため、P1_ENAMEに動的アクションを作成します。

作成した動的アクションの識別の名前はonChange Refresh EMPとします。ページ・アイテムに動的アクションを作成すると、タイミングはデフォルトで、イベントは変更、選択タイプはアイテム、アイテムはP1_ENAMEになります。


TRUEアクションはリフレッシュに変更し、影響を受ける要素の選択タイプとしてリージョンを選び、リージョンとしてEmployeesを選択します。


クラシック・レポートのリージョンEmployeesを選択し、ソースのタイプを表/ビューからSQL問合せに変更します。SQL問合せとして、選択した従業員と同じ部門の従業員が一覧されるよう、以下のSELECT文を記述します。
select
    empno
    , ename
    , job
    , mgr
    , hiredate
    , sal
    , comm
    , deptno
from emp
where deptno in (
    select deptno from emp where ename = :P1_ENAME
)
送信するページ・アイテムとしてP1_ENAMEを指定します。


以上で、バインド変数を使った検索の絞り込みは完成です。

これが最も一般的な実装でしょう。


ページのコピーを作成する



ページのコピーを実行します。作成メニューからコピーとしてのページを実行します。


次のコピーとしてのページを作成として、このアプリケーションのページを選択します。

次へ進みます。


コピー元ページとして、1.バインド変数を選択します。新規ページ番号は2、新規ページ名はV関数を使ったビューとします。

次へ進みます。


ナビゲーションのプリファレンスとして、新規ナビゲーション・メニュー・エントリの作成を選択します。新規ナビゲーション・メニュー・エントリは、ページ名がデフォルトとして設定されます。

次へ進みます。


ラベルなどの値は変更せず、コピーを実行します。


ページのコピーが作成されます。コピーされたページを改変して、それぞれの実装を確認します。


V関数を使ったビューにする



ページのコピーをページ番号２、ページ名をV関数を使ったビューとして作成します。

以下の定義にて、ビューemp_in_same_dept_vを作成します。
create or replace view emp_in_same_dept_v(empno, ename, job, mgr, hiredate, sal, comm, deptno)
as
select
    empno
    , ename
    , job
    , mgr
    , hiredate
    , sal
    , comm
    , deptno
from emp
where deptno in 
(
    select deptno from emp where ename = V('P2_ENAME')
);
ビューを定義しているSELECT文にバインド変数は使えないため、代わりにV関数を使用しています。

レポートのソースのタイプを表/ビューに変更し、表名としてビューEMP_IN_SAME_DEPT_Vを指定しています。送信するページ・アイテムはP2_ENAMEです。


以上で、V関数を使ったビューによる実装は完成です。バインド変数を使った実装と、動作に違いはありません。

ただし、このようなビューの実装はほとんど使い道がありません。
  1. ページ・アイテム名としてP2_ENAMEが埋め込まれていて、ビューとして再利用ができない。
  2. V関数が値を返すには、APEXセッションが開始している必要がある。(APEX_SESSION.CREATE_SESSIONまたはATTACHが呼び出されている必要がある）。
  3. ２、３より、このビューを使った単体テストの実施が難しい。
  4. V関数による値の取得は遅い。
V関数のパフォーマンスについては、Srihari Ravvaさん（Oracle APEXの開発チームの方）が彼のブログ記事 - Oracle APEX SYS_CONTEXT vs V Function - で検証しています。

V関数はつねにセッション・ステートとして保存されている値を返すと思っていたのですが、実際は、送信されたページ・アイテムの値を返すようです。それが無い場合、セッション・ステートの値を返します。また、ページ・アイテムP2_ENAMEのソースのセッション・ステートの保持がリクエストごと(メモリーのみ) - つまりセッション・ステートとして保存されない - であってもV関数は送信されたページ・アイテムが設定されていると、その値を返します。

とはいえ、他に実装の選択肢がある場合、V関数の使用は避けるべきです。


SQL問合せを返すファンクション本体を使う



ページのコピーをページ番号３、ページ名をファンクション本体として作成します。

従業員名を引数として、同じ部門に所属する従業員を一覧するSELECT文を生成するファンクションgen_select_emp_in_same_deptを作成します。SQLインジェクションを抑止するため、DBMS_ASSERT.ENQUOTE_LITERALを使用します。

create or replace function gen_select_emp_in_same_dept(
    p_ename in varchar2
)
return varchar2
as
    l_stmt varchar2(32767);
begin
    l_stmt := 'select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp';
    l_stmt := l_stmt || ' where  deptno in (select deptno from emp where ename = ' || dbms_assert.enquote_literal(p_ename) || ')';
    return l_stmt;
end gen_select_emp_in_same_dept;

ソースのタイプをSQL問合せを返すファンクション本体に変更し、ソースとして以下を記述します。

return gen_select_emp_in_same_dept(:P3_ENAME);

送信するページ・アイテムとしてP3_ENAMEを指定します。


以上で実装は完了です。

この実装では、リテラルの異なるSQLが毎回生成されているため、SELECT文のパース処理もその度に実行されています。そのため、パフォーマンス面に悪い影響があります。

また、gen_select_emp_in_same_deptはOracle APEXのアプリケーションの中からでなくても実行できるため単体テストが可能ですが、返されるのがSELECT文であるため、動的SQLとして実行させる必要があります。


SQLマクロを使う



ページのコピーをページ番号４、ページ名をSQLマクロとして作成します。

従業員名を引数として、同じ部門に所属する従業員を一覧するSELECT文を生成するSQLマクロmacro_select_emp_in_same_deptを作成します。
create or replace function macro_select_emp_in_same_dept(
    p_ename in varchar2
)
return clob sql_macro
as
    l_stmt clob;
begin
    l_stmt := 'select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp';
    l_stmt := l_stmt || ' where  deptno in (select deptno from emp where ename = p_ename)';
    return l_stmt;
end macro_select_emp_in_same_dept;

ソースのSQL問合せを、SQLマクロを使ったSELECT文に変更します。
select
    empno
    , ename
    , job
    , mgr
    , hiredate
    , sal
    , comm
    , deptno
from macro_select_emp_in_same_dept(:P4_ENAME)
送信するページ・アイテムとしてP4_ENAMEを指定します。


SQLマクロを使ったSQLは、単体での実行も可能です。

引数に従業員名としてALLENを指定し、SQLコマンドで上記のソースであるSELECT文を実行した結果になります。



アプリケーション・アイテムを使う



ページのコピーをページ番号5、ページ名をアプリケーション・アイテムとして作成します。

V関数を使ったビューで作成したビューEMP_IN_SAME_DEPT_Vでは、ページ・アイテムP2_ENAMEを内部で参照しています。そのため、ページが変わると再利用ができません。この部分をアプリケーション・アイテムに置き換えます。

アプリケーション・アイテムG_ENAMEを作成します。

共有コンポーネントのアプリケーション・アイテムを開きます。


作成済みのアプリケーション・アイテムが一覧されます。作成をクリックします。


アプリケーション・アイテムの名前をG_ENAMEとします。有効範囲はアプリケーション、セキュリティのセッション・ステート保護として、一番保護が厳しい、制限付き - ブラウザから設定不可を選択します。

アプリケーション・アイテムの作成をクリックします。


アプリケーション・アイテムG_ENAMEが作成されます。


アプリケーション・アイテムG_ENAMEの値で、一覧する従業員を制限するビューEMP_IN_DEPT_Vを作成します。ビューEMP_IN_SAME_DEPT_VのP2_ENAMEをG_ENAMEに置き換えています。
create or replace view emp_in_dept_v(empno, ename, job, mgr, hiredate, sal, comm, deptno)
as
select
    empno
    , ename
    , job
    , mgr
    , hiredate
    , sal
    , comm
    , deptno
from emp
where deptno in 
(
    select deptno from emp where ename = V('G_ENAME')
);

ページ・アイテムP5_ENAMEの値が変更されたときに実行される動的アクションの、リフレッシュの前にTRUEアクションを作成します。

識別のアクションとしてサーバー側のコードを実行を選択します。設定の言語としてPL/SQL、PL/SQLコードとして、以下を記述します。ページ・アイテムP5_ENAMEの値をアプリケーション・アイテムG_ENAMEに設定しています。

:G_ENAME := :P5_ENAME;

送信するアイテムにP5_ENAMEを設定します。戻すアイテムの指定は、画面に戻す値です。G_ENAMEは画面に戻す値では無いので、戻すアイテムとして指定する必要はありません。


レポートのソースの表/ビューとして、EMP_IN_DEPT_Vを指定します。送信するページ・アイテムの指定は不要です。ページ・アイテムP5_ENAMEの値は、onChange Refresh EMPとして作成した動的アクションでサーバーに送信済みです。


以上で実装は完了です。

ビューのコードに埋め込んだアイテムがアプリケーション・アイテムであるため、ビューEMP_IN_DEPT_Vの再利用ができるようになっています。ただし、APEXセッションが開始されている必要があること、V関数のパフォーマンスが良く無い点については変わりありません。


アプリケーション・コンテキストを使う



ページのコピーをページ番号６、ページ名をアプリケーション・コンテキストとして作成します。

アプリケーション・コンテキストを扱う権限をAPEXのワークスペース・スキーマに割り当てます。

grant create any context to <APEXワークスペース・スキーマ>;
grant execute on dbms_session to <APEXワークスペース・スキーマ>;

今回の作業ではAutonomous Databaseを使用しているため、データベース・アクションのSQLより以下のコマンドを実行しました。APEXのワークスペース・スキーマの名前はwksp_apexdevです。

grant create any context to wksp_apexdev;
grant execute on dbms_session to wksp_apexdev;



APEXにて、以下のSQLスクリプトを実行します。アプリケーション・コンテキストEMP_DEPT_CTX、アプリケーション・コンテキストを操作するパッケージEMP_DEPT_CTX_PKG、アプリケーション・コンテキストを使ったビューEMP_IN_DEPT_APPCTX_Vを作成しています。

-- アプリケーション・コンテキストemp_dept_ctxを作成する。
create or replace context emp_dept_ctx using emp_dept_ctx_pkg;
-- アプリケーション・コンテキストを操作するパッケージemp_dept_ctx_pkgを作成する。
create or replace package emp_dept_ctx_pkg is
procedure set_ename(p_ename in varchar2);
end emp_dept_ctx_pkg;
/
create or replace package body emp_dept_ctx_pkg is
procedure set_ename(p_ename in varchar2)
is
begin
dbms_session.set_context('emp_dept_ctx', 'ename', p_ename);
end set_ename;
end emp_dept_ctx_pkg;
/
-- アプリケーション・コンテキストを使ったビューの作成
create or replace view emp_in_dept_appctx_v(empno, ename, job, mgr, hiredate, sal, comm, deptno)
as
select
empno
, ename
, job
, mgr
, hiredate
, sal
, comm
, deptno
from emp
where deptno in
(
select deptno from emp where ename = sys_context('EMP_DEPT_CTX','ENAME')
);
view raw emp_dept_ctx.sql hosted with ❤ by GitHub
アプリケーション・コンテキストを使用する場合、動的アクションによるリフレッシュは利用できません。アプリケーション・コンテキストに値を設定したデータベース・セッションと、レポートのリフレッシュの際に発行されたAjaxコールを処理するデータベース・セッションが異なることがあるためです。アプリケーション・コンテキストに設定した値は、同一のデータベース・セッションからのみ参照することができます。

そのため、ページ・アイテムP6_ENAMEの設定の選択時のページ・アクションをRedirect and Set Valueに変更します。作成済みの動的アクションは削除します。


レンダリング前にプロセスを作成し、アプリケーション・コンテキストに値を設定します。

作成したプロセスの識別の名前はコンテキストの設定とします。タイプはコードの実行です。ソースのPL/SQLコードとして、以下を記述します。

emp_dept_ctx_pkg.set_ename(:P6_ENAME);

アプリケーション・コンテキストEMP_DEPT_CTXにENAMEとして、ページ・アイテムP6_ENAMEの値を設定します。


ソースの表名にEMP_IN_DEPT_APPCTX_Vを指定します。


ページ・アイテムP6_ENAMEを切り替えるたびに、変更が保存されていない旨の警告が表示されます。その警告を抑止するため、ページ・プロパティのナビゲーションの保存されていない変更の警告をOFFに変更します。


以上で、アプリケーション・コンテキストを使った実装は完了です。

アプリケーション・コンテキストを使用すると、V関数のような速度の低下は発生しないようです。そのため、ビューを作る必要がある場合は、V関数ではなくアプリケーション・コンテキストを使用するのが望ましいです。

また、以下のマニュアルに記載があるAPP_ID、APP_USER、APP_SESSIONといったSYS_CONTEXT変数として参照可能な組み込み置換文字列は、パフォーマンス上の利点よりV関数ではなくSYS_CONTEXT変数として参照することが推奨されています。

Oracle APEXアプリケーション・ビルダー・ユーザーズ・ガイド


おおむね実装方法としての推奨は、バインド変数、SQLマクロ、アプリケーション・コンテキストの順になりますが、要件が変わればこの順番も変わります。特に複雑なSQLで単体テストを行う必要がある場合は、SQLマクロやアプリケーション・コンテキストを使ったビューを使った実装は、検討する価値があるでしょう。

以上になります。

今回作成したAPEXアプリケーションのエクスポートを以下に置きました。grant create contextやexecute on dbms_sessionは、アプリケーションのインポート前に実施しておきます。
https://github.com/ujnak/apexapps/blob/master/exports/sample-report-with-parameter.zip

Oracle APEXのアプリケーション作成の参考になれば幸いです。

完
投稿者 時刻:
ラベル: , ,