2021年11月12日金曜日

統計データの表示ページを作成する

 Oracle APEX 21.2のRESTソース・カタログとページ区切りについて紹介する記事の追記です。e-Statの統計表情報取得のREST APIを呼び出し、統計表の一覧を取得するところまでは紹介済みです。この記事では統計表コードを選択し、メタ情報取得と統計データ情報取得を行うREST APIを呼び出して、それぞれ対話モード・レポートとして表示するページを作成します。

Oracle APEX 21.2の新機能の説明ではないので、Oracle APEX 21.2の新機能のシリーズには含めていません。


統計データのページ作成


ページ作成ウィザードを起動し、名前を統計データとした空白ページを作成します。ページが作成されると以下の画面になります。ページ番号は３を想定しています。

統計表コードを保持するページ・アイテムP3_TABLE_INFを作成します。識別の名前をP3_TABLE_INF、タイプはテキスト・フィールド、ラベルは統計表コードとします。設定の[Enter]を押すと送信はON、ソースのセッション・ステートの保持はセッションごと(ディスク)を選択します。


メタ情報を表示する対話モード・レポートを作成します。リージョンの作成を行います。識別のタイトルはメタ情報とします。タイプは対話モード・レポートです。ソースの位置をRESTソースとし、RESTソースにメタ情報取得を選択します。

送信するページ・アイテムは空白です。動的アクションを使ってリージョンをリフレッシュする場合は、ページ・アイテムP3_TABLE_INFを送信するページ・アイテムに含む必要があります。Oracle APEX 21.2よりRESTデータ・ソースのタイプが簡易HTTPでもページ区切りをサポートするようになりましたが、外部フィルタと外部並べ替え基準はサポートしていません。そのため値は空白です。これらはOracle REST Data Servicesでのみサポートされています。

パラメータを開きstatsDataIdを選択します。


e-Statのメタ情報取得のREST APIを呼び出す際のパラメータstatsDataIdとして、ページ・アイテムP3_TABLE_INFの値、つまり統計表コードを渡すように設定します。値のタイプにアイテムを選択し、アイテムとしてP3_TABLE_INFを選びます。


統計データを表示する対話モード・レポートを作成します。識別のタイトルを統計データ、タイプを対話モード・レポート、ソースの位置をRESTソースとし、RESTソースに統計データ取得を選びます。


統計データ取得のパラメータstatsDataIdとして、ページ・アイテムP3_TABLE_INFを割り当てます。statsDataIdはパラメータの最下部にあります。値のタイプにアイテムを選択し、アイテムとしてP3_TABLE_INFを選びます。


以上で一旦ページを実行します。統計表コード(ページ・アイテムP3_TABLE_INF)に0003295482を入力し、Enterを入力すると、統計表民間企業の勤務条件制度等調査のメタ情報と統計データが表示されます。



メタ情報のキャッシュ


統計データはtab、time、cat01、cat02, ..., cat15などとして、コードが返されます。そのため、それらのコードに対応する名称を求めるにはメタ情報を参照する必要があります。

e-Statが返すメタ情報には属性CLASSとして配列が含まれています。Oracle APEXが提供するRESTデータ・ソースの同期化は、属性に配列があるケースに対応していません。そのため、e-Statのメタ情報を取得しデータベースに保存するためのコードを書きます。

取得したメタ情報を保存する表ESTAT_OBJECTSおよびESTAT_CLASSESを作成します。クイックSQLの定義は以下になります。
# prefix: estat
# semantics: default 
objects
    table_inf num
    obj_id vc10
    obj_name vc200
    classes
        code vc10
        name vc200
        level num
        parent_code vc10
	unit vc20

クイックSQLを使ってSQLの生成を行い、生成したSQLスクリプトを実行して表を作成します。


e-Statのメタ情報取得を行うREST APIを呼び出し、表ESTAT_OBJECTS、ESTAT_CLASSESに保存する処理は自動化として作成することにしました。ページ・プロセスに直接記述することもできますし、再利用をしやすくするという意味ではPL/SQLのプロシージャとして作成しても良いです。

以前の記事RESTデータ・ソースを定期的に実行するの最後に自動化をコードから呼び出す方法を紹介していますが、そこでバインド変数に値が渡らないという不具合が見つかっています。この不具合はOracle APEX 21.2で解消されています。ですので、メタ情報取得を行う自動化に統計表コードをバインド変数として渡しています。

コードの中からRESTデータ・ソースメタ情報取得を呼び出すため、静的IDを設定します。共有コンポーネントのRESTデータ・ソースからメタ情報取得を開きます。詳細のセクションにある静的IDとしてESTATMETAを設定します。変更の適用を行います。


続いて自動化を作成します。共有コンポーネントの自動化を開き、作成を開始します。

名前をメタデータ取り込みとし、タイプはオンデマンド(つまり定期実行ではなくプロシージャAPEX_AUTOMATION.EXECUTEより呼び出す)、アクションの開始を問合せ(バインド変数としてパラメータを渡すため)とします。次へ進みます。


アクションの開始のソースを指定します。データ・ソースはローカル・データベース、ソース・タイプはSQL問合せとします。SQL SELECT文を入力には以下を記述します。

select '0003295482' as table_inf from dual

作成した自動化を呼び出す際に、必ずSQL文が引数として与えます。そのため、ここで指定するSQL文は自動化を設定画面から実行したときにだけ使われます。アクションの実行時間は行が返されるです。このプロパティは英語ではExecute Actions WhenでWhenを時間と翻訳してしまっていて、今ひとつ意味がわからなくなっています。

作成をクリックします。


自動化メタデータ取り込みが作成されます。静的IDはプロシージャAPEX_AUTOMATION.EXECUTEによる呼び出し時の引数になります。これはloadmetadataと設定します。


デフォルトで作成されているアクションを、e-Statのメタ情報を取得するように書き換えます。編集するために鉛筆アイコンをクリックします。


名前をメタデータの保存とし、以下をコードとして記述します。与えられた統計表コード(バインド変数:TABLE_INF)が表ESTAT_OBJECTSに無ければ、e-Statのメタ情報取得REST APIを呼び出します。JSON形式の応答をパースし、表ESTAT_OBJECTSとESTAT_CLASSESにぞれぞれ保存します。

declare
    l_context apex_exec.t_context;
    l_params  apex_exec.t_parameters;
    l_columns apex_exec.t_columns;
    l_idx_id    pls_integer;
    l_idx_name  pls_integer;
    l_idx_class pls_integer;
    --
    l_meta_count number;
    -- 
    l_table_inf number;
    l_id number;
    r_object estat_objects%rowtype;
    r_class  estat_classes%rowtype;
    l_elem  json_element_t;
    l_array json_array_t;
    l_class json_object_t;
begin
    -- すでにメタデータが取得済みであるか確認する。
    l_table_inf := to_number(:TABLE_INF);
    select count(*) into l_meta_count from estat_objects where table_inf = l_table_inf;
    if l_meta_count > 0 then
        apex_automation.exit(
            p_log_message => '統計表' || :TABLE_INF || 'のメタデータはキャッシュ済み - ' || l_meta_count
        );
    else
        apex_automation.log_info('統計表' || :TABLE_INF || 'のメタデータをキャッシュします。');
    end if;
    -- メタデータをREST経由で取得する。
    l_params.delete;
    apex_exec.add_parameter(
        p_parameters => l_params,
        p_name => 'statsDataId',
        p_value => :TABLE_INF
    );
    l_context := apex_exec.open_rest_source_query(
        p_static_id => 'ESTATMETA',
        p_parameters => l_params
    );
    l_idx_id := apex_exec.get_column_position(l_context, 'CLASS_OBJ_ID');
    l_idx_name := apex_exec.get_column_position(l_context, 'CLASS_OBJ_NAME');
    l_idx_class := apex_exec.get_column_position(l_context, 'CLASS');
    --
    while apex_exec.next_row(l_context)
    loop
        -- メタデータを表ESTAT_OBJECTSに保存する。
        r_object.table_inf := l_table_inf;
        r_object.obj_id    := apex_exec.get_varchar2(l_context, l_idx_id);
        r_object.obj_name  := apex_exec.get_varchar2(l_context, l_idx_name);
        insert into estat_objects values r_object returning id into l_id;
        -- CLASSとして返されるJSONのオブジェクトまたは配列を表ESTAT_CLASSESに保存する。
        l_elem := json_element_t.parse(apex_exec.get_clob(l_context, l_idx_class));
        if l_elem.is_Array then
            l_array := treat(l_elem as json_array_t);
        else
            -- 要素がひとつの配列を作る。
            l_array := new json_array_t;
            l_array.append(l_elem);
        end if;
        for i in 0..(l_array.get_size() - 1)
        loop
            l_class := json_object_t(l_array.get(i));
            -- apex_automation.log_info(l_class.to_string);
            r_class.object_id   := l_id; -- ESTAT_OBJECTSを指す。
            r_class.code        := l_class.get_string('@code');
            r_class.name        := l_class.get_string('@name');
            r_class.the_level   := l_class.get_number('@level');
            r_class.parent_code := l_class.get_string('@parentCode');
            r_class.unit        := l_class.get_string('@unit');
            insert into estat_classes values r_class;
        end loop;
    end loop;
    apex_exec.close(l_context);
end;

以上でe-Statからメタ情報を取得し保存する自動化が作成できました。保存して実行をクリックして、動作のテストを行います。


キャッシュされた結果はSQLコマンドより、表ESTAT_OBJECTS、ESTAT_CLASSESを検索して確認します。

select * from estat_objects where table_inf = to_number('0003295482')


共有コンポーネントの自動化の実行ログのタブからも、実行結果を確認することができます。


メッセージの数値をクリックすると、出力されたログ・メッセージを確認できます。


作成した自動化メタデータ取り込みを統計データのページが表示されるときに実行されるよう、ページ・プロセスを作成します。

ページ統計データを開き、レンダリング前のヘッダーの前にプロセスを作成します。名前をメタデータのキャッシュ、タイプとしてコードを実行を選択します。PL/SQLコードとして以下を記述します。無駄にプロセスが呼び出されないように、サーバー側の条件としてタイプがアイテムはNULLではない、アイテムとしてP3_TABLE_INFを選択します。
declare
    l_context apex_exec.t_context;
    l_columns apex_exec.t_columns;
    l_sql varchar2(4000);
begin
    -- 10桁の統計表コードを渡す。
    l_sql := 'select ''' || :P3_TABLE_INF || ''' as table_inf from dual';
    apex_exec.add_column(
        p_columns => l_columns,
        p_column_name => 'TABLE_INF'
    );
    l_context := apex_exec.open_query_context(
        p_location => apex_exec.c_location_local_db,
        p_columns => l_columns,
        p_sql_query => l_sql
    );
    apex_automation.execute(
        p_static_id => 'loadmetadata',
        p_query_context => l_context
    );
end;

統計表の一覧から統計表を選択し統計データの表示ができるように、統計表一覧のページの対話モード・レポートの列TABLE_INFを統計データのページへのリンクに変更します。

ページ統計表一覧をページ・デザイナで開き、対話モード・レポート統計表一覧の列TABLE_INFを選択します。

タイプをリンクに変更し、外観の書式マスクとして0000000000(0を10個)を設定します。e-Statの統計表コードは表示が10桁になるように左に0を詰める必要があります。ターゲットをクリックして、リンク・ターゲットを設定します。


リンク・ビルダー・ターゲットでは、ターゲットのタイプとして、このアプリケーションのページ、ページを3とします。アイテムの設定として名前をP3_TABLE_INF、値に#TABLE_INF#を指定します。統計表コードTABLE_INFをページ・アイテムP3_TABLE_INFに渡しています。キャッシュのクリアとして3（ページ番号3に紐づくアイテムのセッション・ステートをクリアするという意味です)、アクションとしてリージョンのリセットを選択し、OKをクリックします。


以上で統計表一覧より統計データのページを開き、その際に対象となっている統計表のメタ情報がキャッシュされるようになりました。

アプリケーションを実行し、統計表一覧の任意の統計表を選択します(列Table Infをクリックします)。


対象として統計表のメタ情報と統計データが表示されます。ただし、まだ表ESTAT_OBJECTS、ESTAT_CLASSESに保存されたデータを使っていないため、メタ情報がキャッシュされたかどうかは統計データのページからは確認できません。


統計表コードをコピーしSQLコマンドから表ESTAT_OBJECTSを検索することで、保存されたメタ情報を参照できます。



統計データのコードの置き換え


表ESTAT_OBJECTS、ESTAT_CLASSESに保存したメタ情報にて、統計データのコードによる表示を置き換えます。

列Value、UnitおよびAnnotation以外はすべてコードが表示されているため、コードを実表示に置き換えるLOVを作成します。共有コンポーネントのLOVを開き、作成をクリックします。

LOVの作成として最初からを選択します。次へ進みます。


最初にcat01のコードを置き換えるLOVとしてLOV_CAT01を作成します。名前はLOV_CAT01、タイプはDynamicを選択します。次へ進みます。


データ・ソースとしてローカル・データベース、ソース・タイプにSQL Queryを選択し、SQL SELECT文を入力に以下を記述します。ここでwhere句の条件として o.obj_id = 'cat01'を与えています。置き換えるコードはcat01からcat15, timeおよびtabがあります。それらのコードを置き換えるLOVも同様に作成しますが、SELECT文の条件はcat01, cat02, cat03, ..., cat15, time, tabと変わります。
select 
     c.name d,
     c.code r
from estat_objects o join estat_classes c
  on o.id = c.object_id
where o.table_inf = to_number(:P3_TABLE_INF)
  and o.obj_id = 'cat01'

戻り列をR、表示列をDとします。作成をクリックします。


LOVとしてLOV_CAT01が作成されます。同様にLOV_CAT02, LOV_CAT03, ... , LOV_CAT15, LOV_TIME, LOV_TABを作成します。コピーをクリックして、作成済みのLOV_CAT01をコピーして、where句の指定をcat02, cat03, ... , time, tabと変更すると速く作成できます。


全部で17のLOVを作成します。


対話モード・レポートの列のラベルとなるページ・アイテムを作成します。作成したページ・アイテムにCAT01_CODE用のラベルとなる設定を行います。

識別の名前をP3_LABEL_CAT01とし、タイプとして非表示を選択します。設定の保護された値をOFFにし、ソースのセッション・ステートの保護としてセッションごと(ディスク)を選択します。デフォルトのタイプを静的とし、静的値にcat01を指定します。例えばcat15など、統計データ自体が無いことがある列があります。その場合メタ情報も無いためラベルが空になってしまいます。その状況を回避するためにデフォルトを設定しています。


同様の設定でページ・アイテムP3_LABEL_CAT02, P3_LABEL_CAT03, ... , P3_LABEL_CAT15, P3_LABEL_TIME, P3_LABEL_TABを作成します。デフォルトの静的値はそれぞれユニークになるように変更します。ページ・アイテムの重複を行うと素早く作成できます。


対話モード・レポートの列の設定を変更します。列CAT01_CODEの設定を行います。

列CAT01_CODEを選択し、タイプをプレーン・テキスト(LOVに基づく)に変更します。そのうえでLOVとしてLOV_CAT01を選択します。ヘッダーは&P3_LABEL_CAT01.を指定します。


同様に列CAT02_CODE, CAT03_CODE, ... , TIME_CODE, TAB_CODEの設定を変更します。

最後に列のラベルを設定するプロセスを作成します。レンダリング前のヘッダーの前の位置に配置します。

名前は列ラベルの設定とします。PL/SQLコードとして以下を記述します。統計表コードP3_TABLE_INFがNULLの場合は実行されないように、サーバー側の条件のタイプとしてアイテムはNULLではないを選択し、アイテムにP3_TABLE_INFを指定します。
begin
    for c in (
        select obj_id, obj_name 
        from estat_objects 
        where table_inf = to_number(:P3_TABLE_INF)
    )
    loop
        case c.obj_id
            when 'tab'   then :P3_LABEL_TAB   := c.obj_name;
            when 'time'  then :P3_LABEL_TIME  := c.obj_name;
            when 'cat01' then :P3_LABEL_CAT01 := c.obj_name;
            when 'cat02' then :P3_LABEL_CAT02 := c.obj_name;
            when 'cat03' then :P3_LABEL_CAT03 := c.obj_name;
            when 'cat04' then :P3_LABEL_CAT04 := c.obj_name;
            when 'cat05' then :P3_LABEL_CAT05 := c.obj_name;
            when 'cat06' then :P3_LABEL_CAT06 := c.obj_name;
            when 'cat07' then :P3_LABEL_CAT07 := c.obj_name;
            when 'cat08' then :P3_LABEL_CAT08 := c.obj_name;
            when 'cat09' then :P3_LABEL_CAT09 := c.obj_name;
            when 'cat10' then :P3_LABEL_CAT10 := c.obj_name;
            when 'cat11' then :P3_LABEL_CAT11 := c.obj_name;
            when 'cat12' then :P3_LABEL_CAT12 := c.obj_name;
            when 'cat13' then :P3_LABEL_CAT13 := c.obj_name;
            when 'cat14' then :P3_LABEL_CAT14 := c.obj_name;
            when 'cat15' then :P3_LABEL_CAT15 := c.obj_name;
            else null;
        end case;
    end loop;
end;


以上でアプリケーションは完成です。ページを保存して実行し、動作を確認します。


任意の統計表コードを選択して、統計データを表示させます。統計表コードとし0003005535を選択した例が以下になります。メタ情報を表示する対話モード・レポートにてORA-6502が発生しています。これは列CLASSとして返される情報が32Kバイトをはるかに超えていて、対話モード・レポートで表示できる制限を超えているためです。ただし、メタ情報の読み込み自体は行われているため、統計データの対話モード・レポートのラベルやコードは適切に置き換えられています。表ESTAT_OBJECTS, ESTAT_CLASSESの内容を表示する対話モード・レポートに変更することが対応になるでしょう。



RESTデータ・ソースの制限


統計データの対話モード・レポートの書式のページごとの行数を10行に変更します。レポートに表示される行数が10行になります。


このときに発行されたREST APIを確認します。ワークスペース・ユーティリティのApplication ExpressビューよりAPEX_WEBSERVICE_LOGを参照します。


発行されているREST APIのリクエストは以下です。startPositionの指定が1、limitは11となっています。つまり1画面に表示できる行だけを取得しています。

https://api.e-stat.go.jp/rest/3.0/app/json/getStatsData?annotationGetFlg=Y&cntGetFlg=N&explanationGetFlg=N&metaGetFlg=N&replaceSpChars=0&statsDataId=0003005535&startPosition=1&limit=11

対話モード・レポートでソートを設定します。


このときに発行されているREST APIのリクエストは以下です。ソートを設定しても同じ呼び出しです。

https://api.e-stat.go.jp/rest/3.0/app/json/getStatsData?annotationGetFlg=Y&cntGetFlg=N&explanationGetFlg=N&metaGetFlg=N&replaceSpChars=0&statsDataId=0003005535&startPosition=1&limit=11

Oracle APEX 21.2よりタイプが簡易HTTPのRESTデータ・ソースについてもページ区切りがサポートされましたが、外部フィルタや外部並べ替え基準はサポートされていません。そのためソートやフィルタリングはサーバー側で行われず、画面上のデータだけが対象になったり(ソート)、データを全件取得する(フィルタ)といった動きをします。


データ件数が多い場合は、対話的にレポートを操作することは困難になります。そのため、タイプが簡易HTTPのRESTデータ・ソースでページ区切りがサポートされましたが、対話モード・レポートのようなページ区切りをサポートしたコンポーネントで使用するためのものではなく、簡易HTTPのRESTデータ・ソースで同期化表の利用を可能にするための機能と理解すべきです。

以上でOracle APEX 21.2のRESTサービスの拡張から派生した、RESTデータ・ソースの使い方の紹介は終了です。

作成したアプリケーションのエクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/estat-app.sql

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

完
投稿者 時刻:
ラベル: , ,