APEX_REGION.OPEN_QUERY_CONTEXT呼び出し時にp_outer_sqlを指定して出力する列を選択する

APEXのPL/SQL APIとして提供されているAPEX_REGION.OPEN_QUERY_CONTEXTの引数のひとつにp_outer_sqlがあります。p_outer_sqlにリージョンのデータ・ソースを囲む形で記述したSELECT文を与えることにより、APEX_REGION.OPEN_QUERY_CONTEXTの出力結果を置き換えることができます。

このp_outer_sqlの使い方のひとつを、以前に記事「ファセットによる検索結果の合計や平均を計算する」で紹介しています。ファセットを適用した検索結果の列SALおよびCOMMの合計、平均、最小値、最大値、件数を別のレポートに表示しています。この例ではAPEX_REGION.OPEN_QUERYに引数p_outer_sqlを指定して、その結果をパイプライン表関数とする、少々複雑な実装を行っています。

本記事では引数p_outer_sqlの、もう少し簡単な使用例を紹介します。こちらの記事「ファセット検索の検索結果をAPEX_REGION.OPEN_QUERY_CONTEXTを呼び出してCSV形式でダウンロードする」にて、ファセット検索の結果をCSVでダウンロードする実装を紹介しています。このダウンロード処理に、引数p_outer_sqlを使って以下の機能を追加します。

1. CSVへ出力する列を選択する。
2. 列MGRとDEPTNOの出力を名前ではなく、生データの数値にする。

出力列を選択するページ・アイテムをP3_PRINT_COLUMNSとして作成します。

ページ・アイテムP3_PRINT_COLUMNSには、以下の設定を行います。

タイプはチェックボックス・グループです。ラベルはPrint Columnsとしていますが、外観のテンプレートにHiddenを選択しているため表示はされません。レイアウトのラベル列スパンを０にして、テンプレート・オプションのStretch From Itemをチェックします。列の数を10としているため（列の数は８）、選択する列は横一列で、画面の左右いっぱいに表示されます。

セッション・ステートのストレージはセッションごと(永続)を選びます。ダウンロード処理を実行するAjaxコールバックのプロセスに引数を渡すことができないため、選択した列を保存しているページ・アイテムP3_PRINT_COLUMNSの値は、変更が発生したときにセッション・ステートに保存します。Ajaxコールバックはセッション・ステートに保存されたページ・アイテムP3_PRINT_COLUMNSの値を参照します。

LOVのタイプは静的値として、表示値と戻り値を設定します。

戻り値は必ずレポート列の列名にします。

ページ・アイテムP3_PRINT_COLUMNSに動的アクションを作成します。タイミングのイベントとして変更を選択します。

TRUEアクションとしてサーバー側のコードを実行を選択します。送信するアイテムにP3_PRINT_COLUMNSを指定し、この動的アクションが呼び出された時に、画面上のページ・アイテムP3_PRINT_COLUMNSの値をデータベースに送信します。データベースは受信した値をセッション・ステートに保存します。ページ・アイテムの値をセッション・ステートに保存するだけであれば、PL/SQLコードの実行は不要です。PL/SQLコードを空にはできないため、null;を記述します。

CSVのダウンロードを実行するPL/SQLコードに、引数p_outer_sqlの指定を追加します。

	declare
	l_outer_sql varchar2(4000);
	l_region_id apex_application_page_regions.region_id%type;
	l_context apex_exec.t_context;
	l_export apex_data_export.t_export;
	begin
	/* クラシック・レポートのリージョンの静的IDより、region_idを取得する */
	select region_id into l_region_id from apex_application_page_regions
	where application_id = :APP_ID and page_id = :APP_PAGE_ID and static_id = 'EMPLOYEES';
	/* outer sqlで出力する列を決める。 */
	l_outer_sql := 'select ' || replace(:P3_PRINT_COLUMS,':',',') || ' from #APEX$SOURCE_DATA#';
	/* MGR, DEPTNOをIDにする。 */
	-- l_outer_sql := 'select ' || replace(:P3_PRINT_COLUMS,':',',') || ' from emp where empno in (select empno from #APEX$SOURCE_DATA#)';
	/* クラシック・レポートのQuery Contextを取得する。 */
	l_context := apex_region.open_query_context(
	p_page_id => :APP_PAGE_ID
	,p_region_id => l_region_id
	,p_outer_sql => l_outer_sql
	);
	l_export := apex_data_export.export (
	p_context => l_context
	,p_format => apex_data_export.c_format_csv
	,p_file_name => 'download' );
	apex_exec.close( l_context );
	apex_data_export.download( p_export => l_export );
	end;

view raw csv-download-with-outer-sql.sql hosted with ❤ by GitHub

p_outer_sqlに以下を渡し、出力する列としてEMPNO、Employee Name、Job、Manager、Departmentを選択してCSVダウンロードを実行します。チェックボックス・グループで複数選択された値は':'（コロン）で区切られています。SELECT文の列として認識されるために','（カンマ）に置き換えています。

l_outer_sql := 'select ' || replace(:P3_PRINT_COLUMS,':',',') || ' from #APEX$SOURCE_DATA#';

置換文字列#APEX$SOURCE_DATA#は、指定したリージョン自体の問い合わせに置き換えられます。この結果セットは、レポート・リージョンに定義された列を持ちます。

以下のCSVファイルがダウンロードされます。

p_outer_sqlに以下を渡すと、列MGRとDEPTNOが数値になります。

l_outer_sql := 'select ' || replace(:P3_PRINT_COLUMS,':',',') || ' from emp where empno in (select empno from #APEX$SOURCE_DATA#)';

APEX_REGION.OPEN_QUERY_CONTEXTの引数p_outer_sqlの使い方の紹介は以上になります。

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

完