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 を作成します。
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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;
プロセスを作成し、レンダリング前 のヘッダーの前 にあるプロセス初期化フォーム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 を作成します。
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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'
-- 空白区切りの文字列を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'
-- 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;
プロセス・ビューを開きます。プロセスを作成し、プロセスプロセス・フォーム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のアプリケーション作成の参考になれば幸いです。
完
