アプリケーションに与えた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ファンクション本体に以下を記述します。

	declare
	l_sql varchar2(32767);
	begin
	l_sql := :P1_SELECT;
	if l_sql is null or l_sql = '' then
	-- ０行を返すSELECT文を戻すと、レポートは表示されない。
	return 'select * from dual where 1 <> 1';
	end if;
	return l_sql;
	end;

view raw dynamic-sql-report-source.sql hosted with ❤ by GitHub

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

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

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

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

	declare
	l_header varchar2(32767);
	l_cursor integer;
	l_col_cnt integer;
	l_desc_tab dbms_sql.desc_tab;
	l_sql varchar2(32767);
	begin
	l_sql := :P1_SELECT;
	if l_sql is null or l_sql = '' then
	-- SELECT文が無ければ列名指定は不要。
	return '';
	end if;
	-- 最初にカーソルをオープンする。
	l_cursor := dbms_sql.open_cursor;
	-- オープンしたカーソルを使って、与えられたSELECT文を解析する。
	dbms_sql.parse(l_cursor, l_sql, dbms_sql.native);
	-- 列情報を取得する。
	dbms_sql.describe_columns(l_cursor, l_col_cnt, l_desc_tab);
	-- 列情報からレポート・ヘッダーを設定する。
	for i in 1 .. l_col_cnt
	loop
	-- 列名をヘッダーに追加する。
	if i = 1 then
	l_header := l_desc_tab(i).col_name;
	else
	l_header := l_header || ':' || l_desc_tab(i).col_name;
	end if;
	apex_debug.info('header: %s', l_header);
	end loop;
	-- カーソルをクローズする。
	dbms_sql.close_cursor(l_cursor);
	return l_header;
	exception
	when others then
	if dbms_sql.is_open(l_cursor) then
	dbms_sql.close_cursor(l_cursor);
	end if;
	/*
	* SELECT文の問題はレポートのソース側で例外が発生する。
	* そのため、ヘッダーの設定では例外は無視する。
	*/
	-- raise;
	end;

view raw dynamic-sql-report-header.sql hosted with ❤ by GitHub

以上でアプリケーションは完成です。アプリケーションを実行すると、記事の先頭の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

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

	set serveroutput on
	DECLARE
	l_cursor INTEGER;
	l_col_cnt NUMBER;
	l_desc_tab DBMS_SQL.DESC_TAB2;
	l_select_stmt VARCHAR2(32767) := '&1'; -- Input SELECT statement
	BEGIN
	-- Open a cursor
	l_cursor := DBMS_SQL.OPEN_CURSOR;
	BEGIN
	-- Parse the SQL statement
	DBMS_SQL.PARSE(l_cursor, l_select_stmt, DBMS_SQL.NATIVE);
	-- Describe columns in the result set
	DBMS_SQL.DESCRIBE_COLUMNS2(l_cursor, l_col_cnt, l_desc_tab);
	-- Output column names and data types
	FOR i IN 1..l_col_cnt LOOP
	DBMS_OUTPUT.PUT_LINE('Column Name: ' || l_desc_tab(i).col_name);
	DECLARE
	l_data_type VARCHAR2(30);
	BEGIN
	CASE l_desc_tab(i).col_type
	WHEN 1 THEN l_data_type := 'VARCHAR2';
	WHEN 2 THEN l_data_type := 'NUMBER';
	WHEN 8 THEN l_data_type := 'LONG';
	WHEN 96 THEN l_data_type := 'CHAR';
	WHEN 97 THEN l_data_type := 'MLSLABEL';
	WHEN 100 THEN l_data_type := 'RAW';
	WHEN 101 THEN l_data_type := 'DATE';
	WHEN 102 THEN l_data_type := 'NAMED PL/SQL TYPE';
	WHEN 104 THEN l_data_type := 'ROWID';
	WHEN 106 THEN l_data_type := 'MIXED';
	WHEN 108 THEN l_data_type := 'REF';
	WHEN 110 THEN l_data_type := 'CLOB';
	WHEN 111 THEN l_data_type := 'BLOB';
	WHEN 112 THEN l_data_type := 'BFILE';
	WHEN 113 THEN l_data_type := 'TIMESTAMP';
	WHEN 114 THEN l_data_type := 'INTERVAL YEAR TO MONTH';
	WHEN 115 THEN l_data_type := 'INTERVAL DAY TO SECOND';
	WHEN 121 THEN l_data_type := 'TIMESTAMP WITH TIME ZONE';
	WHEN 122 THEN l_data_type := 'TIMESTAMP WITH LOCAL TIME ZONE';
	WHEN 123 THEN l_data_type := 'INTERVAL YEAR TO MONTH';
	WHEN 178 THEN l_data_type := 'TIMESTAMP WITH TIME ZONE';
	WHEN 179 THEN l_data_type := 'TIMESTAMP WITH LOCAL TIME ZONE';
	WHEN 180 THEN l_data_type := 'TIMESTAMP WITH TIME ZONE';
	WHEN 181 THEN l_data_type := 'TIMESTAMP WITH LOCAL TIME ZONE';
	WHEN 2000 THEN l_data_type := 'OBJECT';
	WHEN 2001 THEN l_data_type := 'NVARCHAR2';
	WHEN 2002 THEN l_data_type := 'NCHAR';
	WHEN 2003 THEN l_data_type := 'NCLOB';
	WHEN 2004 THEN l_data_type := 'BINARY_FLOAT';
	WHEN 2005 THEN l_data_type := 'BINARY_DOUBLE';
	ELSE l_data_type := 'UNKNOWN';
	END CASE;
	DBMS_OUTPUT.PUT_LINE('Data Type: ' || l_data_type);
	END;
	END LOOP;
	EXCEPTION
	WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('Error parsing SQL statement: ' || SQLERRM);
	END;
	-- Close the cursor
	IF DBMS_SQL.IS_OPEN(l_cursor) THEN
	DBMS_SQL.CLOSE_CURSOR(l_cursor);
	END IF;
	END;
	/

view raw describe-columns-sample-generated-by-Cline.sql hosted with ❤ by GitHub

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

完