アプリケーションに与えたSELECT文を実行して動的にクラシック・レポートを表示する

SELECT文をテキスト領域のページ・アイテムに入力し、実行した結果をクラシック・レポートに表示してみます。主にDBMS_SQL.PARSEとDBMS_SQL.DESCRIBE_COLUMNSを呼び出します。

Oracle APEXのレポートやチャートのソースとなる表やビューまたはSELECT文は、アプリケーション開発時に解析され、見つかった検索対象の列がレポートに設定されます。そのため、ソースのタイプとしてSQL問合せを返すファンクション本体を選択し、SELECT文をアプリケーションの実行時に決定する場合に検索列の順番や数を変更できません。例外として、クラシック・レポートには汎用列名の使用という設定があり、レポートに列のメタデータを設定しない代わりに、検索対象の列を変更することができます。汎用列を使用した場合、列名はCOL01、COL02、COL03、...といった連番が付いた名前になり、汎用列数として設定した数だけ、あらかじめクラシック・レポートに列が定義されます。

今回の記事では、以下のAPEXアプリケーションを作成します。入力したSELECT文の結果をクラシック・レポートに表示します。入力したSELECT文の列定義は、パッケージDBMS_SQLを使って取り出します。

アプリケーションの名前をDynamic SQL Reportとして、空のAPEXアプリケーションを作ります。デフォルトで作成されるホーム・ページにクラシック・レポートを実装します。

ボタンSUBMITはページの送信を行なうだけで、個別のプロセスを呼び出したりはしません。SELECT文を入力したページ・アイテムP1_SELECTの内容をセッション・ステートに保存し、クラシック・レポートのソース内で参照できるようにします。

SELECT文を入力するページ・アイテムをP1_SELECTとして作成します。タイプはテキスト領域です。入力したSELECT文をセッション・ステートに保存するため、セッション・ステートのデータ型としてVARCHAR2、ストレージにセッションごと(永続)を設定します。

リージョンを作成し、タイプをクラシック・レポートとします。

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

汎用列名の使用をオンにし、汎用列数は10とします。汎用列数は必要に応じて変更します。

クラシック・レポートの属性を開きます。

メッセージのデータが見つからない場合に、データが見つかりません。と記述します。

ヘッダーのタイプとしてPL/SQLファンクション本体を選択し、PL/SQLファンクション本体に、列名を : （コロン）で連結した文字列を返すPL/SQLコードを記述します。

以上でアプリケーションは完成です。アプリケーションを実行すると、記事の先頭のGIF動画のように動作します。

今回作成したAPEXアプリケーションのエクスポートを以下に置きました。

https://github.com/ujnak/apexapps/blob/master/exports/dynamic-sql-report.zip

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

余談

VSCodeの拡張機能ClineがPL/SQLを書いてくれるか試してみました。APIプロバイダはローカルで実行しているOllama、モデルとしてhhao/qwen2.5-coder-tools:32bを使っています。Macbook Pro M4の128GBメモリのマシンで実行しています。

% ollama ps

NAME                            ID              SIZE     PROCESSOR    UNTIL              

hhao/qwen2.5-coder-tools:32b    5d17b48771de    43 GB    100% GPU     3 minutes from now    

%

タスクの最初のプロンプトとして、以下を与えています。

Please write a PL/SQL code to retrieve column definition of given select statement by using DBMS_SQL package. input is string that is for SELECT statement and the output is the columns and data types found in select statement in the array.

例外処理が含まれていなかったので、以下のプロンプトを与えました。

please add an exception hander when wrong sql is provided.

作成されたファイルget_column_definitions.sqlをAcceptすると、以下のコマンドを実行して、と案内されました。Run Commandというボタンが表示されたのでクリックすると、VSCodeでターミナルが開いて、コマンドが実行されました。ユーザー名やパスワードが正しく無いので、エラーが発生しました。

sqlplus username/password@database @get_column_definitions.sql "SELECT * FROM your_table"

ユーザー名、パスワードを変更して実行するとcol_type_nameが無いというエラーが発生しました。以下のプロンプトを与えて、修正を要求しました。

pls-00302 has raised. it seems taht col_type_name is not exist . please fix.

col_type_nameとなっていた部分がcol_typeを使うように書き換わり、 col_typeの数値を型名に置き換えるコードが挿入されました。

上記のコードを実行する前に、先頭行にset serveroutput onを記述するように要求しました。

please put "set serveroutput on" as the first line of the script

以上で出来上がったコードがこちら。きちんと動きます。

ローカルのOllamaなので相当遅いですが、動くコードが生成されるのだからすごいものです。

完

クラシック・レポートを使ってヒートマップを実装する

Oracle JETのData Gridを使ってヒートマップを実装する（こちらの記事）のは難しいときのために、クラシック・レポートを使ってヒートマップを実装してみました。Oracle JETでの表示に合わせています。

作成したアプリケーションは以下のように動作します。

データはJETの記事で作成している表EBAJ_DEMO_HOUSE_PRICEを流用します。

このアプリケーションもホーム・ページにすべてを実装しています。

以下よりアプリケーションの作成手順を紹介します。

アプリケーション作成ウィザードを起動し、空のアプリケーションを作成します。名前はClassic Report Heat Mapとします。

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

ページ・デザイナでホーム・ページを開き、クラシック・レポートのリージョンを作成します。

識別の名前はHeat Map、タイプはクラシック・レポートです。ソースのタイプにSQL問合せを返すファンクション本体を選択し、SQL問合せを戻すPL/SQLファンクション本体として、以下を記述します。

このファンクションは以下のSELECT文を返します。PIVOT列は列PERIODの値から決まるため、動的にSQLを作成しています。また、列名は開発時では不明なため、汎用列名の使用をオンにしています。汎用列数に20を指定しているため、Region列を除いた19列がピボット列の上限になります。

select 
  * 
from 
  (
    select 
      region, 
      period, 
      price 
    from 
      ebaj_demo_house_price
  ) pivot (
    sum(price) for period in (
      'January2013' as January2013, 'February2013' as February2013, 
      'March2013' as March2013, 'April2013' as April2013, 
      'May2013' as May2013, 'June2013' as June2013, 
      'July2013' as July2013, 'August2013' as August2013, 
      'September2013' as September2013, 
      'October2013' as October2013, 'November2013' as November2013, 
      'December2013' as December2013, 'January2014' as January2014, 
      'February2014' as February2014, 'March2014' as March2014, 
      'April2014' as April2014, 'May2014' as May2014
    )
  )

不要な修飾を無くすために、外観のテンプレートにBlack with Attributes (No Grid)を選択しています。また、JavaScriptとCSSの中でレポート・リージョンを指定するために、静的IDとしてHEATMAPを設定します。

汎用列の列名を保持するページ・アイテムを作成します。汎用列は20個あるため、ページ・アイテムとしてP1_COL01からP1_COL20の20個のページ・アイテムを作成します。タイプは非表示です。

クラシック・レポートのソースであるPL/SQLファンクション内で、これらのページ・アイテムに列名を設定しています。

汎用列のヘッダーにページ・アイテムに設定した値が反映されるように、それぞれの列のヘッダーに置換文字列&P1_COL01.（列名に合わせて&P1_COL01.から&P1_COL20.まで）を設定します。

ページを実行して、これまでの作業を確認します。

表EBAJ_DEMO_HOUSE_PRICEの内容がピボットされてレポートに表示されていることが確認できます。

数値が表示されているセルを、ヒートマップとして正方形にしたり色付けできるようにします。

列COL01はリージョンを表示しているため除外し、列COL02からCOL20までの列の書式のHTML式に以下を記述します。

<div class="ht-cell">
 <div class="ht-text">
   #COL02#
  </div>
</div>

また、これらの列でソートすることは無いので、ソートのソート可能はオフにします。HTML式に含まれる置換文字列#COL02#は、列名に合わせて変更します。

今回のソート可能のように、複数のアイテムのプロパティを同じ値にする場合、対象をすべて選択した上で値を変更すると設定漏れを防げます。

ページ・プロパティのCSSのインラインに以下を記述します。年月の列名を縦方向に印字する、数値のセルを正方形にする、数値の色を白にしてセルの中央に配置するようにしています。それ以外に、CSS変数の値を変更し、セルのパディングを除いています。

これまでの変更で、レポートは以下のように表示されます。

セルの色付けは、クラシック・レポートのリフレッシュ後の動的アクションで行います。

クラシック・レポートに動的アクションを作成します。タイミングのイベントはリフレッシュ後です。

TRUEアクションとしてJavaScriptコードの実行を選択し、設定のコードに以下を記述します。JETの実装と同じ色味になるように、色付けのクラスとしてOracle JETで定義されているクラスを設定しています。

JETの色に関するクラス定義を参照するため、ページ・プロパティのCSSのファイルURLに以下を設定します。

#JET_CSS_DIRECTORY#redwood/oj-redwood-notag-min.css

この時点でページを実行しレポートを表示させてみると、以下のようにセルが色付けされません。

ページ・ロード時はリフレッシュ後のイベントが発生しないため、動的アクションが実行されません。

ページ・ロード時にリフレッシュ後のイベントが発生するようにします。

クラシック・レポートの属性を開き、パフォーマンスの遅延ロードをオンにします。遅延ロードをオンにすると、ページ・ロードとレポート表示が非同期で実行されるようになり、レポート表示が完了した時点でリフレッシュ後イベントが発生します。

クラシック・レポートでは行ごとに背景色を変えたり、ポインタが行に乗ったときに行をハイライトさせるといった機能があります。これらの機能はセルの色に影響を与える可能性があるため、無効化します。

クラシック・レポートの属性に含まれる外観のテンプレート・オプションを開き、Alternating RowsおよびRow Highlightingの双方をDisableにします。

以上でアプリケーションは完成です。

今回作成したAPEXアプリケーションのエクスポートを以下に置きました。

https://github.com/ujnak/apexapps/blob/master/exports/classic-report-heat-map.zip

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

完

各種レポートでのソート設定について

Oracle APEXで一般に使用されている対話モード・レポート、対話グリッド、クラシック・レポートでは、ソースのSQL問合せにORDER BY句を含めず、列に対してソートの設定を行なうことが推奨されています。

以下より、サンプル・データセットのEMP/DEPTに含まれる表EMPをソースとした対話グリッド、対話モード・レポート、クラシック・レポートのそれぞれで、給与の降順で１０人だけをレポートに表示する設定を紹介します。

対話グリッド

ソースのSQL問合せを以下とします。

select * from emp

列SALのユーザーの有効化のソートをオンにします。

対話グリッドの属性のページ区切りのタイプをページにします。スクロールの場合、対話グリッドに表示される行数はリージョンの大きさで決まります。ページにすると表示される行数を指定できます。

対話グリッドのアクションのデータからソートを開きます。

ソート列としてSalを追加し、方向に降順を選択して保存します。

アクションの書式のページごとの行数を開き、10を選択します。

これでレポートに給与の降順で10行が（最初に）表示されるようになりました。

アクションのレポートの保存を実行し、デフォルトのレポート形式として設定します。

対話モード・レポート

ソースのSQL問合せを以下とします。

select * from emp

列SALのユーザーの有効化のソートをオンにします。

対話モード・レポートのアクションのデータからソートを開きます。

１番目の列にSal、方向に降順を選択し、適用します。

アクションの書式のページごとの行数を開き、10を選択します。

これでレポートに給与の降順で10行が（最初に）表示されるようになりました。

アクションのレポートのレポートの保存を開きます。

保存(開発者にのみ表示)にデフォルトのレポートとして保存を選択し、適用します。

デフォルトのレポート・タイプとして主を選択し、適用します。

これで、現在のレポートがデフォルトの表示形式として保存されました。

クラシック・レポート

ソースのSQL問合せを以下とします。

select * from emp

列SALを選択し、ソートのデフォルト順序に１、方向に降順を設定します。ユーザーによるソート方向の変更を許可しない場合は、ソート可能をオフにします。

クラシック・レポートではレポートの表示形式を保存することができず、デフォルトの表示形式というのもありません。ソート順を限定する場合は、他の列のソートのソート可能をオフにする必要があります。

クラシック・レポートの属性のレイアウトの行数のタイプに静的値を選択し、行数として10を設定します。

これでレポートに給与の降順で10行が（最初に）表示されるようになりました。

クラシック・レポートではOrder by項目（並替え基準）を設定できます。並替え基準については以下の記事で紹介しています。

APEX 22.1の新機能 - 並替え基準を使う

https://apexugj.blogspot.com/2022/07/new-221-sort-item.html

今回の記事は以上です。

完

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の指定を追加します。

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のアプリケーション作成の参考になれば幸いです。

完