日日是Oracle APEX: 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に取り込みます。

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

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

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

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

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

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

作成をクリックします。

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

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

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

itemsとして複数のPurchase Orderのドキュメントが含まれる。
POドキュメントには複数のShippingInstructionが含まれる。
ShippingInstructionには複数のPhoneが含まれる。
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