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.
例外処理が含まれていなかったので、以下のプロンプトを与えました。
作成されたファイルget_column_definitions.sqlをAcceptすると、以下のコマンドを実行して、と案内されました。Run Commandというボタンが表示されたのでクリックすると、VSCodeでターミナルが開いて、コマンドが実行されました。ユーザー名やパスワードが正しく無いので、エラーが発生しました。please add an exception hander when wrong sql is provided.
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なので相当遅いですが、動くコードが生成されるのだからすごいものです。
完
