2020年8月11日火曜日

Oracle APEXでJSONを使う

 Oracle APEXでアプリケーションを作成するには、必ずではないですが、データベースに表がある、ということを前提としています。Oracle APEXを開発している部門を統括しているSenior Vice PresidentのMike Hichwaのツイートにあるように、

With a strong data model the application almost write itself.

であり、Oracle APEXは単にUIを作るためのツールというより、データモデル（スキーマ定義といってもよい）からUIを自動的に生成するツールとして作られています。

とはいえ、データモデルを作る、または、スキーマを定義する、というのは大変な作業であり、さらに言うと、現在あるすべての要件を盛り込むことや、将来発生しうる要件を考慮すること、そういったことができるのかどうかもわかりません。

Oracle DatabaseはJSON形式のドキュメントを列に保存することができます。スキーマの定義を厳密に行えないケースに対応するために、JSONを使うことができます。これから、Oracle APEXでJSONを扱う方法について紹介します。

Oracle DatabaseでJSONを扱う方法を、OracleのBeda HammerschmidtがLIve SQLとYouTubeで紹介しています。タイトルはUsing JSON for FlexfieldsまたはUsing JSON to Implement Flexfieldsです。ここで使用されているEMP表の定義を少々簡単にして、Oracle APEXによるユーザー・インターフェースを開発します。

クイックSQLの以下のモデルから表FF_EMPを作成します。

# prefix: ff
emp
    empno    num /pk
    ename    vc50
    job      vc50
    hiredate
    sal      num
    flex     json
JSON形式のドキュメントを保存する列は、flexとして定義されています。

SQLの生成、SQLスクリプトを保存、レビューおよび実行を順次クリックします。


スクリプト・エディタが開きます。

列FLEXの定義をCLOBからBLOBに変更します。また、Autonomous Databaseであればformat osonの指定を付加します。

    flex                           blob check (flex is json format oson)

JSONのデータはCLOBとしても保存可能ですが、日本語を含むデータをCLOBで保存すると内部的にUTF-16のエンコーディングに変換されるため効率が良くありません。

このスクリプトを実行すると、表FF_EMPが作成されます。


作成された表FF_EMPを元に、アプリケーションを作成します。

アプリケーションの作成を実行します。確認画面が開くので、そこでもアプリケーションの作成をクリックします。


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

アプリケーションの名前はJSON flexfieldの実装とします。フォーム付きの対話モード・レポートのページだけを使用するので、ホーム・ページは削除します。

以上でアプリケーションの作成を実行します。


これで元になるアプリケーションが作成されました。


アプリケーションを実行し、JSONを保存する列FLEXを確認します。

対話モード・レポートのページが開きます。データは未入力であるため、一行もありません。作成をクリックします。


表FF_EMPにデータを入力するフォームが開きます。列FLEXのデータ型はBLOBであるため、ファイル・セレクタがUIになっています。ファイルとしてJSONのデータが存在している場合は、そのファイルを選択すると、列FLEXへJSONデータが保存されます。


Live SQLで紹介されているように、列FLEXとして

{"shoeSize":12, "skills":["oracle", "c", "java"]}

を登録することにします。つまり、数値フィールドであるshoeSizeと、複数の値を持つskillsをフォーム画面から与え、列FLEXにJSON形式で保存します。そのデータを呼び出すときはJSON形式のデータから、shoeSizeとskillsを取り出すことになります。

ページ・デザイナでページ番号２のフォームのページを開きます。

列FLEXに対応するページ・アイテムP2_FLEXを、ビルド・オプションでコメント・アウトします。


ページ・アイテムP2_FLEXの代わりに、shoeSizeに対応するページ・アイテムを追加します。

名前をP2_SHOESIZEとします。タイプは数値フィールドです。ラベルは靴のサイズ、ソースの使用はセッション・ステートの値がNULLの場合のみを設定し、セッション・ステートのストレージはリクエストごと（メモリーのみ）を設定します。


次に、skillsに対応するページ・アイテムを追加します。名前をP2_SKILLSとします。タイプはテキスト・フィールドです。ラベルはスキル（複数）、それ以外はP2_SHOESIZEと同じにします。


上記を保存し、アプリケーションを実行して修正したフォームを開くと、ファイル・セレクタの代わりに靴のサイズとスキル（複数）の２つのフィールドが追加されていることを確認できます。


ページ・デザイナに戻り、列FLEXからP2_SHOESIZE、P2_SKILLSを取り出すプロセス、および、P2_SHOESIZE、P2_SKILLSを列FLEXへ保存するプロセスを作成します。

従業員番号を引数として、靴のサイズとスキルを取り出すプロシージャread_from_jsonを作成します。

create or replace procedure read_from_json(
p_empno in number
,p_shoesize out number
,p_skills out varchar2
)
as
l_json json_object_t;
l_blob blob;
l_json_array json_array_t;
l_apex_array apex_t_varchar2;
begin
select flex into l_blob from ff_emp where empno = p_empno;
if l_blob is not null then
-- BLOBをJSONオブジェクトとしてパース
l_json := json_object_t(l_blob);
-- 靴のサイズを取り出す
p_shoesize := l_json.get_string('shoeSize');
-- スキルの配列を取り出し、空白区切りの文字列にする
l_json_array := l_json.get_array('skills');
for i in 0..l_json_array.get_size()-1
loop
apex_string.push(l_apex_array, l_json_array.get_string(i));
end loop;
p_skills := apex_string.join(l_apex_array, ' ');
end if;
end read_from_json;
view raw read_from_json.sql hosted with ❤ by GitHub

プロセスを作成し、レンダリング前のヘッダーの前にあるプロセス初期化フォームFf Empの直下に配置します。

識別の名前はJSON読み取り、タイプはAPIの呼出しを選択します。設定のタイプとしてPL/SQL Procedure or Functionを選択し、作成済みのプロシージャREAD_FROM_JSONが呼び出されるようにします。サーバー側の条件としてタイプにアイテムはNULLではないを選択し、アイテムとしてP2_EMPNOを指定します。


パラメータp_empno、p_shoesize、p_skillsにはそれぞれページ・アイテムP2_EMPNO、P2_SHOESIZE、P_SKILLSがデフォルトで割り当てられます。


JSONから靴のサイズとスキルを読み取るプロセスを作成しました。次にそれらをJSON形式にし、書き込むプロセスを作成します。

従業員番号を引数として、靴のサイズとスキルをJSONに変換して書き込むプロシージャwrite_to_jsonを作成します。

create or replace procedure write_to_json(
p_empno in number
,p_shoesize in number
,p_skills in varchar2
)
as
l_json json_object_t;
l_json_array json_array_t;
l_blob blob;
l_apex_array apex_t_varchar2;
begin
-- 空のJSONオブジェクトを作成
l_json := new json_object_t;
-- 靴のサイズを追加
l_json.put('shoeSize', p_shoesize);
-- 空白区切りの文字列をJSON配列にする
l_apex_array := apex_string.split(p_skills, ' ');
l_json_array := new json_array_t;
for i in 1..l_apex_array.count
loop
l_json_array.append(l_apex_array(i));
end loop;
-- スキルのJSON配列をオブジェクトに追加
l_json.put('skills', l_json_array);
-- JSON配列を保存
l_blob := l_json.to_blob;
update ff_emp set flex = l_blob where empno = p_empno;
/*
* flex = l_json.to_blobとすると以下のエラーが発生する。からなずBLOBの変数に入れ直す。
* ORA-40573: PL/SQL JSONオブジェクト・タイプの使用が無効です。
*/
end write_to_json;
view raw write_to_json.sql hosted with ❤ by GitHub

プロセス・ビューを開きます。プロセスを作成し、プロセスプロセス・フォームEf Empの直下に配置します。

識別の名前はJSON書き込み、タイプはAPIの呼出しを選択します。設定のタイプとしてPL/SQL Procedure or Functionを選択し、作成済みのプロシージャWRITE_TO_JSONが呼び出されるように設定します。サーバー側の条件としてタイプにリクエストは値に含まれるを選択し、値としてCREATE,SAVEを指定します。


両方のプロシージャで、Oracle Databaseが提供しているJSONを扱う機能を使用しています。

取り出しと書き込みのプロセスが登録されたので、実際にデータの入力、編集を行います。フォームを開いて、靴のサイズ（ここでは１２）、スキル（ここではoracle c java）を登録して確認します。


作成をクリックしフォームを閉じた後、再度フォームを開いて設定した値を確認します。

最後の対話モード・レポートのSQLに靴のサイズとスキルを表示するよう、変更を加えます。対話モード・レポートのページを開き、ソースのSQL問合せを以下に変更します。
select EMPNO,
       ENAME,
       JOB,
       HIREDATE,
       SAL,
       e.flex.shoeSize "SHOESIZE",
       e.flex.skills "SKILLS"
  from FF_EMP e


対話モード・レポートの表示は以下のようになります。


Oracle APEXでJSON形式のドキュメントを扱う方法の紹介は以上になります。

データの活用を考えたとき、スキーマとして定義できるに越したことはありませんが、そうも行かない場合は多々あるかと思います。そのような場合、JSONが扱えるのはアプリケーションを開発する側に色々なオプションを与えてくれるでしょう。

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

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

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