2023年6月2日金曜日

JSONが保存された列を対象としたレポートとフォームを作る

Oracle APEXは表の定義情報を参照することにより、レポートの列やフォームのアイテムを生成します。列にJSONが保存されている場合は、その内容からレポートの列やフォームのアイテムを生成するといったことはできません。

なんとなくJSON_DATAGUIDEとDBMS_JSON.CREATE_VIEWを使うことで、JSON列を対象としたレポートとフォームの作成が容易になるように思ったので実装してみました。

最初にデータを準備します。

サンプル・データセットのEMP/DEPTに含まれる表EMPを、JSONとして保存する表EMPJを作成します。

create table empj (
    "_ID" number primary key,
    document blob check (document is json)
);

表EMPの主キーは列EMPNOですが、この表EMPJに列EMPNOを作成すると、列DOCUMENTのJSONのデータにもEMPNOが含まれていることから、列名が競合します。そのため、競合しない名前"_ID"という列を作成し、主キーとしています。


表EMPよりJSONデータを生成し、表EMPJに入れ替えます。

insert into empj select empno, json_object(*) from emp;


列DOCUMENTのJSONデータガイドを取得し、それを元にビューEMP_JVを作成します。

declare
l_dataguide clob;
begin
select json_dataguide(document,dbms_json.format_hierarchical) into l_dataguide from empj;
dbms_json.create_view(
viewname => 'EMP_JV'
,tablename => 'EMPJ'
,jcolname => 'DOCUMENT'
,dataguide => l_dataguide
);
end;
view raw create_view_json.sql hosted with ❤ by GitHub

作成したビューEMP_JVより、表EMPJの内容を確認します。

select * from emp_jv;

JSONなのでHIREDATEがISO8601形式になっていますが、JSON形式で保存されているデータが表形式で確認できています。


 作成されたビューEMP_JVの列の型を確認してみます。

select column_name, data_type, data_length from all_tab_cols where table_name = 'EMP_JV';

COLUMN_NAMEDATA_TYPEDATA_LENGTH
JOBVARCHAR216
MGRVARCHAR24
SALNUMBER22
COMMVARCHAR22
EMPNONUMBER22
ENAMEVARCHAR28
DEPTNONUMBER22
HIREDATEVARCHAR232
_IDNUMBER22

この定義は、以下のJSONデータガイドが元になっています。

select json_dataguide(document,dbms_json.format_hierarchical) from empj;

JSONデータガイドの出力です。
{
  "type": "object",
  "o:length": 1,
  "properties": {
    "JOB": {
      "type": "string",
      "o:length": 16,
      "o:preferred_column_name": "JOB"
    },
    "MGR": {
      "type": "string",
      "o:length": 4,
      "o:preferred_column_name": "MGR"
    },
    "SAL": {
      "type": "number",
      "o:length": 4,
      "o:preferred_column_name": "SAL"
    },
    "COMM": {
      "type": "string",
      "o:length": 2,
      "o:preferred_column_name": "COMM"
    },
    "EMPNO": {
      "type": "number",
      "o:length": 4,
      "o:preferred_column_name": "EMPNO"
    },
    "ENAME": {
      "type": "string",
      "o:length": 8,
      "o:preferred_column_name": "ENAME"
    },
    "DEPTNO": {
      "type": "number",
      "o:length": 2,
      "o:preferred_column_name": "DEPTNO"
    },
    "HIREDATE": {
      "type": "string",
      "o:length": 32,
      "o:preferred_column_name": "HIREDATE"
    }
  }
}
ビューEMP_JVができたので、これを元にAPEXのアプリケーションを作成します。

アプリケーション作成ウィザードを起動し、空のアプリケーションを作成します。

名前はJSONフォームとします。


アプリケーションが作成されたら、ページ作成ウィザードを起動し、ビューEMP_JVをソースとした対話モード・レポートとフォームのページを作成します。


対話モード・レポートを選択します。


 ページ定義のページ番号は２、名前は従業員一覧とします。フォーム・ページを含めるはオンにします。フォーム・ページ番号は３、フォーム・ページ名は従業員編集とします。フォーム・ページ・モードはドロワーとします。

データ・ソースの表/ビューの名前としてEMP_JVを指定します。ビューEMP_JVは更新できませんが、これはAPEXのプロセスにコードを記述して対応します。

次へ進みます。


主キー列1として_ID(Number)を選択します。

ページの作成をクリックします。


ページが作成されたら、対話モード・レポートを表示します。

ビューEMP_JVを読んでいるだけなので、対話モード・レポートは問題なさそうです。


編集アイコンをクリックし、フォームを開きます。

ビューEMP_JVの列からページ・アイテムが作成されています。また、ページ・アイテムの値の設定もできています。


ビューは更新できないため、プロセス・フォーム従業員編集の設定のターゲット・タイプをPL/SQL Codeに変更し、以下のコードを記述します。

declare
l_json clob;
begin
case
when :APEX$ROW_STATUS = 'C' then
select json_object(
key 'EMPNO' value to_number(:P3_EMPNO)
,key 'JOB' value :P3_JOB
,key 'MGR' value to_number(:P3_MGR)
,key 'SAL' value to_number(:P3_SAL)
,key 'COMM' value to_number(:P3_COMM)
,key 'DEPTNO' value to_number(:P3_DEPTNO)
,key 'ENAME' value :P3_ENAME
,key 'HIREDATE' value to_date(:P3_HIREDATE,'YYYY-MM-DD"T"HH24:MI:SS')
) into l_json from dual;
insert into empj values(:P3_EMPNO, l_json) returning "_ID" into :P3__ID;
when :APEX$ROW_STATUS = 'U' then
update empj
set document = json_transform(
document,
set
-- '$.EMPNO' = :P3_EMPNO
'$.JOB' = :P3_JOB
,'$.MGR' = to_number(:P3_MGR)
,'$.SAL' = to_number(:P3_SAL)
,'$.COMM' = to_number(:P3_COMM)
,'$.DEPTNO' = to_number(:P3_DEPTNO)
,'$.ENAME' = :P3_ENAME
,'$.HIREDATE' = to_date(:P3_HIREDATE,'YYYY-MM-DD"T"HH24:MI:SS')
) where "_ID" = :P3__ID;
when :APEX$ROW_STATUS = 'D' then
delete from empj where "_ID" = :P3__ID;
end case;
end;
view raw json_process.sql hosted with ❤ by GitHub

また、行のロックはビューEMP_JVではなく、表EMPJに対して実施します。行のロックをPL/SQL Codeに変更し、以下のコードを記述します。（列DOCUMENTの定義にformat osonを付加している場合は、行のロックはNoにします。）


declare
r empj%rowtype;
begin
select * into r from empj where "_ID" = :P3__ID for update;
end;
view raw lock-row-json.sql hosted with ❤ by GitHub


以上でアプリケーションは完成です。

簡単な例で確認しただけですが、レポートやフォームを素早く作ることはできているので、アプローチとしては悪くなさそうです。

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

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

完
投稿者 時刻:
ラベル: