Oracle APEX 24.1のRESTデータ・ソースでサポートされたデータ型の配列を確認する

Oracle APEX 24.1のRESTデータ・ソースでは、受信したJSONレスポンスに含まれるJSON配列を文字列ではなくJSON配列として認識するようになりました。RESTデータ・ソースのデータ・プロファイルのデータ型は配列になります。

JSON配列を扱えるようになったことにより、JSONドキュメントに含まれている階層構造の情報（親子関係のある情報）を扱うことができます。

Oracle APEX 24.1のRESTデータ・ソースの拡張については、Oracle APEXのアーキテクトのCarsten CzarskiさんがOracleの公式ブログの記事で解説されています。

APEX 24.1: REST Data Sources and nested JSON responsesh

https://blogs.oracle.com/apex/post/rest-data-sources-and-nested-json-responses

APEXアプリケーションを作成し、ブログ記事に沿って新しく追加された機能を確認していきます。

元の記事では階層を含むJSONドキュメントを返すREST APIについて、すでに作成済みのURLを紹介しています。本記事ではRESTサービスから作成します。

最初にOracle Databaseのサンプル・スキーマのOrder Entryに含まれているJSONデータを、データベースに取り込みます。以前にJSONのサンプル・データをデータベースに取り込む手順は紹介していますが、もう少し簡略化した手順でデータベースに取り込んでみます。

PurchaseOrders.dmpを取り込む表としてEBAJ_PURCHASE_ORDERSを作成します。今回はデータベースとして23aiを使用しているため、JSONコレクション表として作成します。

create json collection table ebaj_purchase_orders;

表を作成したのち以下のスクリプトを実行し、GitHubにあるPurchaseOrders.dmpを表EBAJ_PURCHASE_ORDERSに取り込みます。

	declare
	l_response clob;
	e_http_failed exception;
	e_po_failed exception;
	l_pos integer;
	l_start integer := 1;
	l_line varchar2(32767);
	l_po json;
	l_id number;
	l_count integer := 0;
	l_blob blob;
	begin
	delete from ebaj_purchase_orders;
	apex_web_service.clear_request_headers;
	l_response := apex_web_service.make_rest_request(
	p_url => 'https://raw.githubusercontent.com/oracle-samples/db-sample-schemas/main/order_entry/PurchaseOrders.dmp'
	,p_http_method => 'GET'
	);
	if apex_web_service.g_status_code <> 200 then
	raise e_http_failed;
	end if;
	dbms_output.put_line('l_response length = ' || dbms_lob.getlength(l_response));
	/*
	* dmp file is JSONL(newline delimited json)
	* import each line as JSON
	*/
	loop
	l_pos := dbms_lob.instr(l_response, apex_application.LF, l_start);
	l_line := dbms_lob.substr(l_response, (l_pos - l_start), l_start);
	l_po := json(l_line);
	l_id := json_value(l_po, '$.PONumber');
	if l_id is null then
	/* Skip if no PONumber in Purchase Order document */
	dbms_output.put_line(apex_string.format('Skip line#%s, %s', (l_count+1), l_line));
	else
	-- dbms_output.put_line('PONumber = ' || l_id);
	/* for JSON type */
	insert into ebaj_purchase_orders values(l_po);
	/* for CLOB type */
	-- insert into ebaj_purchase_orders values(l_line);
	/* for BLOB type */
	-- l_blob := apex_util.clob_to_blob(l_line);
	-- insert into ebaj_purchase_orders values(l_blob);
	l_count := l_count + 1;
	end if;
	exit when l_pos = 0;
	l_start := l_pos + 1;
	end loop;
	commit;
	dbms_output.put_line('Total Records = ' || l_count);
	end;

view raw import_purchase_orders.sql hosted with ❤ by GitHub

9999行のPurchase Orderのドキュメント（いわゆる発注書）が読み込まれます。

データの準備ができたので、この表を元にRESTサービスを作成します。

SQLワークショップのRESTfulサービスを開き、有効なオブジェクトを選択します。

AutoRESTが有効化されたオブジェクトが一覧されます。AutoRESTオブジェクトの作成をクリックします。

オブジェクト・タイプは表、オブジェクトとして先ほど作成した表EBAJ_PURCHASE_ORDERSを選択します。オブジェクト別名にpurchaseordersを設定し、完全なURLに表名が含まれないようにします。認可が必要はいいえとします。

RESTデータ・ソースを作成する際に、ここで表示されている完全なURLを指定します。この値をコピーして保存しておきます。

作成をクリックします。

表EBAJ_PURCHASE_ORDERSをREST APIを通して参照できるようになりました。

ブラウザに完全なURLを入力し、データを確認してみます。

Purchase Orderのドキュメントには、以下の階層構造があることがわかります。

1. itemsとして複数のPurchase Orderのドキュメントが含まれる。
2. POドキュメントには複数のShippingInstructionが含まれる。
3. ShippingInstructionには複数のPhoneが含まれる。
4. POドキュメントには複数のLine Item（いわゆる品目）が含まれる。

このRESTサービスを元にRESTデータ・ソースを作成します。

その前にいくつか注意点を記録しておきます。

Oracle APEX 24.1.1のRESTfulサービスの画面よりAutoREST定義を削除しようとすると、ORA-2290のエラーが発生しました。

代替となる削除方法は２通りあります。ひとつ目の方法は、プロシージャORDS.ENABLE_OBJECTを引数p_enabledにfalseを渡して呼び出し、オブジェクトに対して適用したAutoRESTを無効化します。

begin
    ords.enable_object(
        p_enabled => false
        ,p_object => 'EBAJ_PURCHASE_ORDERS'
    );
end;

もう一つの方法は、データベース・アクションのRESTよりAutoRESTを開き、オブジェクトの無効化を実行します。

また、JSONコレクション表やJSONを保存するデータ型をJSONではなく、CLOBやBLOBとしている場合は、AutoRESTではJSONドキュメントを適切な形式で返しません。

その場合は、モジュールとテンプレートを作成し、GETハンドラに以下のSELECT文を記述します。JSON型に変換できる場合は、JSONファンクションを使用します。

select json(data) data from ebaj_purchase_orders

JSON型が使えない場合はエスケープを抑止する列に別名を付け、その先頭に"{}"を付加します。

select data "{}data" from ebaj_purchase_orders -- CLOBの場合

select to_clob(data) "{}data" from ebaj_purchase_orders -- BLOBの場合

RESTサービスが作成できました。

これからAPEXアプリケーションを作成し、本題のRESTデータ・ソースを作成してJSON配列の扱いを確認します。

アプリケーションの作成を呼び出し、空のAPEXアプリケーションを作成します。名前はPurchase Ordersとします。

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

アプリケーションが作成されたら、共有コンポーネントを開きます。

RESTデータ・ソースを開きます。

作成済みのRESTデータ・ソースが一覧されます。作成をクリックし、Purchase OrdersのREST APIを呼び出すRESTデータ・ソースを作成します。

RESTデータ・ソースの作成は最初から行います。

次へ進みます。

RESTデータ・ソース・タイプとして簡易HTTPを選択します。名前はPurchase Ordersとします。URLエンドポイントとして、作成したREST APIの完全なURLを入力します。

次へ進みます。

新規作成またはすでに作成済みのリモート・サーバーの設定を確認します。

次へ進みます。

ページ区切りタイプとしてページ区切りなしを選択します。

次へ進みます。

認証が必要ですはオフです。

検出をクリックします。

REST APIを呼び出し取得したJSONドキュメントを解析し、属性の名前やデータからデータ型を推定します。

データ・プロファイルを開き、推定されたデータ型を確認します。

行セレクタとしてitemsが認識されています。つまり、それぞれのPOドキュメントは属性itemsに配列として含まれています。

認識されたデータ・プロファイルには、いくつかデータ型がARRAYの行が含まれます。セレクタdata.LineItemsやdata.ShippingInstructions.Phoneです。

RESTデータ・ソースの作成を実行します。

RESTデータ・ソースPurchase Ordersが作成されます。作成された内容を確認するため、Purchase Ordersを開きます。

PL/SQLコードからRESTデータ・ソースを呼び出す際に使用する静的IDを確認します。今回はpurchase_ordersとなっていました。

データ・プロファイルの編集を開きます。

データ・プロファイルDATA_LINEITEMS（セレクタ：data.LineItems）のデータ型が配列であり、それを親列としたデータ・プロファイルとしてPART_UPCCODE、PART_UNITPRICE、PART_DESCIPTION、QUANTITY、ITEMNUMBERが作成されています。

この他にデータ・プロファイルDATA_SHIPPINGINSTRUCTIONS_PHONE（セレクタ：data.ShippingInstructions.Phone）のデータ型が配列であり、それを親列としたデータ・プロファイルとしてTYPEとNUMBER_が作成されています。

この他にデータ・プロファイルLINKSのデータ型が配列として認識されています。データ・プロファイルのLINKS、REL、HREFはORDSが自動的に付与する属性であるため、共通をNoに変更し、ページ作成ウィザードによるレポート作成時に列として含まれないようにします。

この共通はOracle APEX 24.1で、データ・プロファイルに追加された属性です。

変更の適用をクリックします。

以上でRESTデータ・ソースの作成は完了です。

作成したRESTデータ・ソースPurchase Ordersをソースとした、APEXのレポートを作成します。

ページ・デザイナでホーム・ページを開き、新たにリージョンを作成します。

識別の名前はPurchase Orders、タイプは対話モード・レポートとします。ソースの位置にRESTソースを選択し、RESTソースとしてPurchase Ordersを設定します。

ソースとしてRESTソースを選択すると、リージョンの属性としてデータ・プロファイルと配列列が現れます。

以下では列LINKSは不要なので、コメント・アウトしています。

対話モード・レポートは以下のように表示されます。列DATA_LINEITEMSおよびDATA_SHIPPINGINSTRUCTIONS_PHONEともに、JSONがそのまま表示されます。

同様に対話モード・レポートを作成し、データ・プロファイルの配列列としてDATA_LINEITEMSを指定します。レポートに選択できる配列列は１つのみです。

配列列を設定すると、それを親列としたデータ・プロファイルが列として追加されます。今回の例ではPART_UPCCODE、PART_UNITPRICE、PART_DESCRIPTION、QUANTITY、ITEMNUMBERが列として追加されています。

データプロファイルのデータ型が配列となっている列はすべて列から取り除かれます。今回の例では、DATA_LINEITEMS、DATA_SHIPPINGINSTRUCTIONS_PHONE、LINKSです。

対話モード・レポートは以下のように表示されます。PONumberは一意の数値ですが、LineItemsは複数あるため、レポートには同じPONumberが複数現れます。

ローカル後処理を有効にして、LineItemの単価（PART_UNITPRICE）と数量を掛け合わせて、Purchase Orderごとの合計を計算してみます。

ローカル後処理のタイプにSQL問合せを選択し、SQL問合せに以下を記述します。

select 
    DATA_USER,
    DATA_PONUMBER,
    DATA_REFERENCE,
    DATA_COSTCENTER,
    SUM(PART_UNITPRICE * QUANTITY) as ORDER_TOTAL
  from #APEX$SOURCE_DATA#
  group by 
    DATA_USER,
    DATA_PONUMBER,
    DATA_REFERENCE,
    DATA_COSTCENTER

対話モード・レポートは以下のように表示されます。Order TotalにPuchase Orderごとの合計金額が表示されます。

新たに追加された列Order Totalは最初、非表示になっている場合があります。そのときはアクション・メニューの列を開いて、表示列に追加します。

対話グリッドもRESTデータ・ソースの配列列を扱うことができます。対話グリッド以外でも、RESTデータ・ソースが扱えるコンポーネントは概ね配列列を指定できます。

対話グリッドでは、複数の対話グリッドを使ってマスター・ディテール関係を設定できます。

最初にPurchase Orderを保持する対話グリッドを作成します。データ・プロファイルの配列列は、設定しません。

列DATA_PONUMBERを選択し、検証の必須の値をオン、ソースの主キーをオンにします。

Line Itemを表示する対話グリッドを作成します。すでに作成済みの対話グリッドPurchase Ordersをコピーし、識別の名前をLine Itemsに変更します。

列DATA_PONUMBERを選択し、マスター・ディテールのマスター列としてDATA_PONUMBERを選択します。

あとは、対話グリッドPurchase Ordersに表示されている列をコメント・アウトします。

対話グリッドを表示すると以下のように表示されます。

Oracle APEXが提供しているAPIのAPEX_EXEC.OPEN_REST_SOURCE_QUERYも、配列列に当たる引数p_array_column_nameをサポートしています。

本記事で参照しているCarsten Czarskiさんのプログに載っているコードと処理内容は同じですが、APEXアプリケーション内で動作するように改変しました。

ページのレンダリング前にプロセスを作成し、以下のコードを実行します。ページ・アイテムP5_OUTPUTに結果を出力します。

	declare
	l_context apex_exec.t_context;
	l_porefidx pls_integer;
	l_itemnoodx pls_integer;
	l_itempridx pls_integer;
	l_itemquidx pls_integer;
	l_output clob;
	l_line varchar2(32767);
	/*
	* CLOBに文字列を１行出力する。
	*/
	procedure lob_write(
	p_clob in out nocopy clob,
	p_line varchar2
	)
	as
	l_length number;
	l_line varchar2(32767);
	begin
	l_length := length(p_line);
	dbms_lob.writeAppend(
	lob_loc => p_clob
	,amount => l_length
	,buffer => p_line
	);
	/* 改行の印刷 */
	dbms_lob.writeAppend(
	lob_loc => p_clob
	,amount => 1
	,buffer => apex_application.LF
	);
	end lob_write;
	begin
	dbms_lob.createTemporary(
	lob_loc => l_output
	,cache => false
	,dur => DBMS_LOB.CALL
	);
	/*
	* 参照元;
	* https://blogs.oracle.com/apex/post/rest-data-sources-and-nested-json-responses
	*/
	l_context := apex_exec.open_rest_source_query(
	p_static_id => 'purchase_orders',
	--
	-- use the p_array_column_name to pass in the Array Column which this query
	-- should operate on.
	p_array_column_name => 'DATA_LINEITEMS',
	p_max_rows => 100 );
	l_porefidx := apex_exec.get_column_position( l_context, 'DATA_REFERENCE' );
	l_itemnoodx := apex_exec.get_column_position( l_context, 'ITEMNUMBER' );
	l_itempridx := apex_exec.get_column_position( l_context, 'PART_UNITPRICE' );
	l_itemquidx := apex_exec.get_column_position( l_context, 'QUANTITY' );
	lob_write(l_output,
	'|' || lpad( 'REFERENCE', 20, ' ' )
	|| ' ' || lpad( 'ITEM#', 20, ' ' )
	|| ' ' || lpad( 'PRICE', 10, ' ' )
	|| ' ' || lpad( 'QUANTITY', 10, ' ' ));
	lob_write(l_output,
	'|'
	|| lpad( '-', 63, '-' ));
	while apex_exec.next_row( l_context ) LOOP
	lob_write(l_output,
	'|' || lpad( apex_exec.get_varchar2( l_context, l_porefidx ), 20, ' ' )
	|| ' ' || lpad( apex_exec.get_varchar2( l_context, l_itemnoodx ), 20, ' ' )
	|| ' ' || lpad( apex_exec.get_varchar2( l_context, l_itempridx ), 10, ' ' )
	|| ' ' || lpad( apex_exec.get_varchar2( l_context, l_itemquidx ), 10, ' ' )
	);
	end loop;
	apex_exec.close( l_context );
	apex_session_state.set_value(
	p_item => 'P5_OUTPUT'
	,p_value => l_output
	);
	dbms_lob.freeTemporary(l_output);
	exception
	when others then
	apex_exec.close( l_context );
	raise;
	end;

view raw apex-exec-handle-array-column.sql hosted with ❤ by GitHub

出力先となるページ・アイテムP5_OUTPUTの識別のタイプはテキスト領域とします。

外観の高さに40を設定し、高さを広げておきます。また表示が崩れないように詳細のカスタム属性で等幅フォントを使用するように設定します。

style="font-family: monospace;"

以上の設定を行いページを実行すると、以下のように表示されます。

続いて引数p_array_column_nameは指定せず、APEX_EXEC.OPEN_ARRAYとAPEX_EXEC.NEXT_ARRAY_ROWを呼び出し、JSONの階層構造を辿ります。

レンダリング前のプロセスで以下のコードを実行します。

	declare
	l_context apex_exec.t_context;
	l_output clob;
	l_line varchar2(32767);
	procedure lob_write(
	p_clob in out nocopy clob,
	p_line varchar2
	)
	as
	l_length number;
	l_line varchar2(32767);
	begin
	l_length := length(p_line);
	dbms_lob.writeAppend(
	lob_loc => p_clob
	,amount => l_length
	,buffer => p_line
	);
	dbms_lob.writeAppend(
	lob_loc => p_clob
	,amount => 1
	,buffer => apex_application.LF
	);
	end lob_write;
	begin
	dbms_lob.createTemporary(
	lob_loc => l_output
	,cache => false
	,dur => DBMS_LOB.CALL
	);
	l_context := apex_exec.open_rest_source_query(
	p_static_id => 'purchase_orders',
	p_max_rows => 10 );
	while apex_exec.next_row( l_context ) LOOP
	lob_write(l_output,
	'| PO: ' || apex_exec.get_varchar2( l_context, 'DATA_REFERENCE' ) );
	apex_exec.open_array( l_context, 'DATA_LINEITEMS' );
	while apex_exec.next_array_row( l_context ) loop
	lob_write(l_output,
	'| #'
	|| apex_exec.get_varchar2( l_context, 'ITEMNUMBER' )
	|| ': '
	|| apex_exec.get_varchar2( l_context, 'QUANTITY' )
	|| ' x '
	|| apex_exec.get_varchar2( l_context, 'PART_DESCRIPTION' )
	|| ', '
	|| to_char( apex_exec.get_number( l_context, 'PART_UNITPRICE' ), 'FM99990D00' )
	);
	end loop;
	dbms_output.put_line( null );
	apex_exec.close_array( l_context );
	end loop;
	apex_exec.close( l_context );
	apex_session_state.set_value(
	p_item => 'P6_OUTPUT'
	,p_value => l_output
	);
	dbms_lob.freeTemporary(l_output);
	exception
	when others then
	apex_exec.close( l_context );
	raise;
	end;

view raw apex-exec-handle-array-column-2.sql hosted with ❤ by GitHub

ページを実行すると、以下のように表示されます。

今回の記事は以上になります。

今回の記事の説明で使用したAPEXアプリケーションのエクスポートを以下に置きました。

https://github.com/ujnak/apexapps/blob/master/exports/sample-rds-array-purchase-orders.zip

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

完

Oracle APEX 24.1の新しい入力イベントについて

Oracle APEX 24.1より動的アクションのタイミングのイベントに入力が追加されました。テキスト・フィールドまたはテキスト領域に文字が入力されたタイミングで、イベントが発行されます。

以下のGIF動画では、上のテキスト・フィールドに文字が入力されたときに、動的アクションにより下のテキスト・フィールドに入力された文字をコピーしています。デバッグ・モードを有効にしているため、動的アクションが呼び出される度にJavaScriptコンソールに実行履歴が表示されています。概ね１文字入力（または削除）ごとに動的アクションが呼び出されています。

上に配置されているテキスト・フィールドP1_SOURCEに動的アクションを作成しています。タイミングのイベントに入力を指定しています。

TRUEアクションで、入力したテキストを、下に配置されているページ・アイテムP1_TARGETにコピーします。

識別のアクションは値の設定、設定のタイプの設定にJavaScript式を選択し、JavaScript式としてthis.triggeringElement.valueを記述します。

影響を受ける要素にコピー先となるテキスト・フィールドを設定します。選択タイプはアイテム、アイテムはP1_TARGETとします。

設定の変更イベントの禁止はオフ、実行の初期化時に実行はオフにします。

以上で、GIF動画で確認できるうように、文字を入力する度に、文字のコピーが実施されます。

ブラウザ上でJavaScriptのコーディング経験があれば、この入力イベントがHTML要素に対するinputイベントであることに気が付くでしょう。

以下のコードをページ・プロパティのJavaScriptのページ・ロード時に実行に記述しても、同じ動作をします。作成済みの動的アクションはコメント・アウトします。

document.getElementById("P1_SOURCE").addEventListener('input', (event) => {
    apex.items.P1_TARGET.setValue(
        event.target.value
    );
});

動的アクションで実装していないため、デバッグ・モードでもコンソールに呼び出し履歴は表示されません。

入力イベントを受けて、JavaScriptのコードによりページ・アイテムに値を設定したときは、そのページ・アイテムでは入力イベントは発生しません。

ページ・アイテムP1_TARGETに動的アクションを作成し、入力イベントを拾ってページ・アイテムP1_TARGETに設定された値をP1_TAREGT_TO_TARGETにコピーします。

入力された値をページ・アイテムP1_TARGET_TO_TARGETにコピーします。

JavaScriptにより入力された文字列では入力イベントは発行されないため、ページ・アイテムP1_TARGET_TO_TARGETに文字はコピーされません。大元のページ・アイテムP1_SOURCEに設定した動的アクションの値の設定の変更イベントの禁止はオフなので、イベントの発生は禁止されていません。

ページ・アイテムP1_TARGETに設定した動的アクションのタイミングのイベントを変更にすると、ページ・アイテムP1_TARGET_TO_TARGETに文字がコピーされます。

JavaScriptによる文字の入力でも変更イベントは発生するため、ページ・アイテムP1_TARGET_TO_TARGETにP1_SOURCEに入力した文字列が設定されます。

ただし、ページ・アイテムP1_SOURCEに設定した動的アクションのTRUEアクションで、変更イベントの禁止をオンにすると、P1_TARGETに文字列がコピーされても変更イベントは発生しません。

結果としてP1_TARGET_TO_TARGETへの文字列のコピーは行われません。

ページ・アイテムP1_SOURCEに設定した動的アクションのタイミングのイベントを変更にします。

イベントが変更の場合、文字列が確定したときにイベントが発生します。そのため、まとまった文字列がコピーされます。以下ではP1_SOURCEでの変更イベントの禁止をオンにしているため、P1_TARGET_TO_TARGETへの文字列のコピーは行われていません。

動的アクションの変更イベントと同等なJavaScriptのコーディングは以下になります。変更イベントの禁止はitemインターフェースのsetValueの第３引数に対応します。

document.getElementById("P1_SOURCE").addEventListener('change', (event) => {
    apex.items.P1_TARGET.setValue(
        event.target.value, null, true
    );
});

Oracle APEX 24.1で追加された入力イベントの説明は以上です。

簡単なアプリケーションですが、説明に使用したAPEXアプリケーションのエクスポートを以下に置きました。

https://github.com/ujnak/apexapps/blob/master/exports/new-input-event-for-dynamic-action.zip

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

完