Oracle Spatialの機能をOracle APEXで使用する

海外では、Autonomous Database for APEX Developersというプログラム名で、Oracle Databaseが持つ機能をOracle APEXによるアプリケーションから活用する、というワークショップが展開されています。資料や手順は一般に公開されていますので、自分で試してみました。

Autonomous Database for APEX Developersの資料は、GitHubで公開されています。
https://github.com/oracle/cloudtestdrive/blob/master/ATP/APEX/readme.md
ここに含まれるLab 200, Add Spatial to your APEX appについて、どのような作業になったか記載していきます。

ライセンスやマニュアルではOracle Spatial and Graphとして、ひとつのまとまりになっています。この演習ではその中の、Oracle Spatialとして提供される地理空間を扱う機能を使用します。

準備

すでにOracle Cloudのアカウントは取得済みであり、Always FreeのAutonomous DatabaseのインスタンスとOracle APEXのワークスペースも作成済みであれば、準備は完了しています。そのまま、次のステップに進むことができます。

このワークショップはAutonomous Database上で実行することが想定されています。そのための、Oracle Cloudのアカウント取得の手順についてPrerequisite for the workshop として説明されています。まだOracle Cloudのアカウントを持っていない場合は、こちらのリンクからサインアップを行います。それぞれの手順については、次のマニュアル、Oracle Cloudスタート・ガイドの無料Oracle Cloudプロモーションへのサインアップに記載されています。Oracle Cloudのアカウント取得が最初のステップです。

次に、ワークショップで使用するデータベースを用意します。Lab 100, First steps with ATPとして、Autonomous Transaction Processingのデータベースの作成について手順が示されています。このワークショップ全体として、無料トライアルの範疇で、かつ、Oracle APEXのワークスペース名を"WORKSHOPATP"と固定して行うことが想定されています。

とはいえ、気軽に作業を行ってみたいので、今回はAlways Freeのインスタンスを使うことにしました。準備の手順は、以前に書いたこちらのブログ記事「APEXからOCIのオブジェクト・ストレージを操作する - その３」の、Autonomous Databaseの作成で記載されている内容を実施します。ワークスペース名は自由に決めて構いません。

Oracle Spatialの機能をOracle APEXのアプリケーションで使用する

この演習では、あらかじめ提供されているOracle APEXのアプリケーションをインポートし、そのアプリケーションにOracle Spatial and Graphが提供する機能を使った処理を追加します。

作成するアプリケーションは、ドローンの飛行許可を与えて良いかどうかを判定する、というものです。ドローンを飛行させる位置をリクエストとして受け取って、データベースにあらかじめ保存している市街地の情報を参照し、リクエストされた位置が市街地に含まれていれば、不許可、含まれていなければ許可する、といった判定を行います。

すでに作成済みのアプリケーションに、以下のふたつの機能を実装します。

• 住所から緯度経度を取得する - ジオコーディングを実装する
• Oracle Spatialが提供する空間演算子を使用して、上記の座標が市街地(ポリゴン)に含まれるかどうかを判定する

Oracle Spatial and Graphの開発者ガイドはこちらのリンクから参照できます。

APEXアプリケーションをインポートする

機能追加を行う元となるAPEXアプリケーションをインポートします。GitHubのこちらから、f105.sqlをダウンロードします。
GitHubの画面上でDownloadをクリックします。

ブラウザの設定に依存するとは思うのですが、画面にPL/SQLのコードが表示されます。

これがインポートされるアプリケーションのコードですので、ファイルに保存します。以下はMacOS上のChromeを例にしていますが、大抵のブラウザのファイル・メニューに、ページを別名で保存...もしくは別名でページを保存...というコマンドとして、表示されているページをファイルに保存する機能はあるかと思います。そして、f105.sql.txtもしくはf105.sqlというファイル名で、この内容をファイルに保存します。

ファイルに保存した後、ワークスペース名がWORKSHOPATP以外でもエラーなくアプリケーションのインポートが終了できるように、内容を変更します。ファイルに含まれる"Insert into WORKSHOPATP."という記載を"Insert into "へ置き換え、データの挿入の対象となっている表の指定からスキーマ名WORKSHOPATPを除きます。sedを使って実施した例は以下になります。

sed -e 's/Insert into WORKSHOPATP./Insert into /' f105.sql.txt > f105.sql

Oracle APEXのワークスペースにログインし、アプリケーション・ビルダーを開いて、インポートを実行します。

インポートのファイルのインポートとして、編集済みのファイルf105.sqlを指定します。ファイル・タイプはデフォルトで指定される、データベース・アプリケーション、ページまたはコンポーネントのエクスポートのままにし、次へ進みます。

これ以降のインポート・ウィザードからの質問はすべてデフォルトを選択して進みます。

以下の画面が表示されるまで、かなりの時間がかかります。

インポートが正常に完了すると、緑のチェックの表示を含む画面になります。

作成されたアプリケーションを確認する

作成されたアプリケーションは、ページ５の対話レポートと、そこから呼び出されるページ６のフォームを含みます。ホーム・ページには対話レポートを開くナビゲーションがないため、アプリケーション・ビルダーから直接ページ５の対話レポートを実行します。

対話レポートには、ドローンの飛行許可リクエストの一覧が表示され、そこから、それぞれのリクエストの編集画面を開くことができます。

編集画面に許可、不許可を判定する機能を追加していきます。

ジオコーディングを追加する

最終目標は、ドローンの飛行をリクエストする住所が市街地に含まれるかどうかを確認することですが、まず、リクエストされた住所から緯度(latitude)と緯度(longitude)を求める必要があります。この住所から座標を得る処理をジオコーディング(Geo-coding)と呼びます。

このジオコーディングを行う公開されたWebサービスはいくつかありますが、この演習ではオラクルが提供しているeLocationサービスを使用しています。ライセンスや利用許諾について、こちらに記載があります。詳しくはリンク先を確認していただきたいと思いますが、最初に"Oracle Maps Cloud is a cloud-based map data service for use with licensed Oracle applications and services."という記載があることより、オラクルが提供しているアプリケーションおよびサービスからの利用に限定されています。今回はAutonomous Databaseから呼び出すため、特に問題なく利用できます。住所と座標のデータはHERE Technologiesから提供されています。日経の記事によると日本の対応も行っており、サービスの説明にも"Geocoding provides comprehensive coverage in 196 countries"とあり日本が入っていないはずはないのですが、オラクルが提供しているeLocationサービスは残念なことに、日本に対応していないようです。ですので、実際にアプリケーションを作成する際には、別のジオコーダーを使う必要があります。

GoogleのジオコーディングAPIをOracle APEXにて使う方法について、いくつかの記事をネット上で見つけることができます。こちらの料金表からは一ヶ月$200(最大70,000リクエスト)までは無料で利用できるとのことです。無料枠があるとはいえ、演習で有料サービスは扱いにくいため、GoogleのジオコーディングAPIは使用しません。また別途、紹介できればと思います。

作成済みのアプリケーションは、Oracle Elocation Geocoderという動的アクションとして利用できるプラグインが組み込まれています。

このプラグインを使って、eLocationサービスのジオコーディングAPIの呼び出しを行います。このプラグインへ与える入力は以下になります。

• カンマ区切りで繋げた、通り(street)、郵便番号(postal code)、市(city)の情報
• 国名

戻り値は、APEXコレクションとして返されます。この中で重要なのは以下のふたつのカラムです。

• N002: 与えられた住所の経度
• N003: 与えられた住所の緯度

APEXコレクションについては、マニュアルのこちらが参考になります。端的にいえば、通常のデータベースの表となんら変わるところはないのですが、データ保存(こちらはINSERT文ではなく、API呼び出しによる)および取り出し時(こちらはSELECT文の実行)にAPEXのセッションに紐付けられるという特徴があり、名前を付けたコレクションへは、セッションが継続している限りページ・リクエストをまたがってアクセスすることが可能になります。保存したデータを取り出すにはAPEX_COLLECTIONSビューを対象としたSELECT文を実行します。このビュー定義についてはこちらに記載があります。実際にeLocationサービスを呼び出した結果を取り出す際には、以下のSQLを発行します。セッションIDは指定しませんが、現在のセッションで保存されたデータのみが検索対象になります。

SELECT N002, N003
from APEX_COLLECTIONS where collection_name = 'GEOCODER_RESULTS'
and n002 is not null and n003 is not null
order by seq_id;

ジオコーディングAPIの結果は、与えられた住所から複数の地点が対象になることにより、複数の緯度経度のペアが返る場合があります。今回の演習では、実装を単純にするため、最初に返された値を判定に使用し、それ以外は無視しています。

ページ・アイテムP6_CONCATENETED_ADDRESSを追加する

ページ６のフォーム・リージョンDrone Fright Requestに、ページ・アイテムP6_CONCATENATED_ADDRESSを追加します。リージョンDrone Fright Requestを開き、アイテム上でコンテキスト・メニューを開いて、ページ・アイテムの作成を実行します。

名前としてP6_CONCATENATED_ADDRESS、タイプを非表示とします。

ボタンOBTAIN_FLY_ZONE_INFOを追加する

リージョン・ボタン上でコンテキスト・メニューを開いて、ボタンの作成を実行します。

ボタン名としてOBTAIN_FLY_ZONE_INFO、ラベルはObtain Fly Zone Infoを指定します。そして動作のアクションとして、動的アクションで定義を選択します。

動的アクションを追加する

はじめにジオコーディングAPIに渡す住所を、P6_CONCATENATED_ADDRESSに設定するアクションを定義します。ボタンOBTAIN_FLY_ZON_INFO上でコンテキスト・メニューを開き、動的アクションの作成を実行します。

作成された動的アクションは名前が新規になっています。分かりやすくなるように飛行域確認に変更します。タイミングとして設定されているイベント、選択タイプ、ボタンはそれぞれ、クリック、ボタン、OBTAIN_FLY_ZONE_INFOとなっているはずですが、確認しておきます。

Trueアクションの設定を行います。表示となっているTrueアクションを選択し、アクションを値の設定、タイプの設定をJavaScript Expression、JavaScript式として以下を入力します。P6_ADDRESS, P6_POSTAL_CODE, P6_CITYをカンマで区切って連結しています。

$v("P6_ADDRESS").replace(",", " ") + 
", " +
$v("P6_POSTAL_CODE").replace(",", " ") + 
", " +
$v("P6_CITY").replace(",", " ")

この値の変更という動作によって、別の動的アクションが起動されないよう、変更イベントの禁止をONにします(今回は別のアクションは無いので、ONでもOFFでも動作に違いは発生しません)。そして、上記のJavaScript式の結果が設定される項目として、影響を受ける要素の選択タイプをアイテム、アイテムをP6_CONCATENATED_ADDRESSとします。さらに実行オプションとして、初期化時に実行をOFF（ページを開いたときに実行される必要がないため)、結果を待機をON(後続となるアクションにて、設定された結果を参照できるようにするため）に指定します。

このアクションにより、P6_CONCATENATED_ADDRESSにジオコーディングAPIの引数となる住所が作成されます。

続いて、この住所を引数としてジオコーディングAPIを呼び出すアクションを作成します。動的アクションのTrue上でコンテキスト・メニューを開き、TRUEアクションの作成を実行します。

値の設定に続くTrueアクションとして、Oracle Elocation Geocoder [プラグイン]を選択します。設定のItem containing Country CodeはP6_COUNTRY、Item containing address linesはP6_CONCATENATED_ADDRESSを指定します。実行オプションの初期化時に実行はOFFとします。

設定のGeocoding type、Collection Nameはデフォルトで、GeocodingおよびGEOCODER_RESULTSとなっています。Geocoding typeは、これ以外にReverse Geocodingも指定可能です。Reverse Geocodingとは、座標から住所を求める操作です。今回は使用しません。

このアクションは値の取得を待たずに終了します(そのため、実行オプションに結果を待機がありません)。その代わりに、値が取得できるとイベントが通知されます。そのイベントによって起動される動的アクションを設定することにより、ジオコーディングAPIの戻り値を画面に表示します。

左ペインを動的アクション・ビューの表示に切り替え、イベント上でコンテキスト・メニューを開き、動的アクションの作成を実行します。

作成された動的アクションの名前をジオコード取得成功とし、タイミングとして、イベントにelocation_geocoder_success [Oracle Elocation Geocoder]を選択、選択タイプをJavaScript式、JavaScript式にdocumentを指定します。

続けて、表示となっているTrueアクションを選択し、アクションをPL/SQLコードの実行に変更します。そして、PL/SQLコードとして以下に示すコードを入力します。戻すアイテムとしてP6_PERMIT、変更イベントの禁止はOFF(値の入力によってラベルの大きさが変わるようになっているため、イベントを禁止してはいけません)、初期化時に実行をOFF、結果を待機はONとします。

declare
    cursor c_geocoded_address
    is
        SELECT n002, n003
        from apex_collections where collection_name = 'GEOCODER_RESULTS'
        and n002 is not null and n003 is not null
        order by seq_id;
    r_geocoded_address c_geocoded_address%rowtype;
    lon varchar2(100);
    lat varchar2(100);
    coordinates MDSYS.SDO_GEOMETRY;
    overlap_count number;
    result varchar2(100) := '';
begin
    open c_geocoded_address;
    fetch c_geocoded_address into r_geocoded_address;
    lon := r_geocoded_address.n002;
    lat := r_geocoded_address.n003;
    close c_geocoded_address;
    if lon is not null and lat is not null then
        result := result || 'Address found: ' || '[' || lon || ',' || lat || ']';
    else
      result := result || 'Address not found';
    end if;
    :P6_PERMIT := result;
exception
    when others then
        :P6_PERMIT := sqlerrm;
end;

APEXコレクションGEOCODER_RESULTSより、先頭行のN002(経度)、N003(緯度)を取り出し、ページ・アイテムP6_PERMITへ設定しています。

ジオコーデイングAPIの呼び出しの実装はこれで完了です。動作を確認してみましょう。

ページ５の対話レポートを開き、適当なリクエストの編集画面を開きます。Obtain Fly Zone Infoボタンをクリックし、Permitted?の項目に座標値が表示されることを確認します。

ドローンの飛行リクエスト地点が市街地に入らないことを検証する

飛行リクエストに記載されている住所から、座標(緯度 - 経度)を求めることができました。この座標が市街地に入らないことを検証するには、市街地の定義情報が必要です。市街地のデータは、アプリケーションのインポートに含まれるサポート・スクリプトが実行されることにより、表URBAN_AREA_UKが作成され、その表にロードされています。

表URBAN_AREA_UKの定義は以下です。この中の"GEOM"列がOracle Spatialが提供するSDO_GEOMETRY型として定義されていて、それぞれの市街地の領域が登録されています。

CREATE TABLE  "URBAN_AREA_UK" 
   ( "SCALERANK"   　　　　NUMBER(38,0), 
 "FEATURECLA" 　　　　VARCHAR2(50 CHAR) COLLATE "USING_NLS_COMP", 
 "AREA_SQKM" 　　　　　NUMBER, 
 "MIN_ZOOM"    　　　　　NUMBER, 
 "GEOM"            　　　　　　"MDSYS"."SDO_GEOMETRY" , 
 "STUDIO_ID"   　　　　　　NUMBER
   )  DEFAULT COLLATION "USING_NLS_COMP" 
 VARRAY "GEOM"."SDO_ELEM_INFO" STORE AS SECUREFILE LOB 
 VARRAY "GEOM"."SDO_ORDINATES" STORE AS SECUREFILE LOB
/

GEOM列を含む、市街地を定義する表URBAN_AREA_UKに保持される行は以下のようなINSERT文によって入力されています。

Insert into URBAN_AREA_UK (SCALERANK,FEATURECLA,AREA_SQKM,MIN_ZOOM,GEOM,STUDIO_ID) values (9,'Urban area',11.659,7.7,MDSYS.SDO_GEOMETRY(2003, 4326, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1), MDSYS.SDO_ORDINATE_ARRAY(-3.62936273541405, 57.656023725336, -3.62936823910738, 57.6560284078993, -3.62937442179765, 57.6560321484047, -3.62947207804757, 57.6560809765297, -3.62961856242254, 57.6561542187173, -3.62962329256217, 57.6561562541272, -3.62962776599323, 57.6561576233396, -3.62972374689068, 57.6561816185639, -3.62986859296981, 57.6562299005905, -3.62987190661818, 57.6562308655271, -3.62996956286821, 57.6562552795897, -3.6299730763682, 57.6562560103527, -3.63011778940148, 57.6562801291917, -3.63021370349318, 57.6563041077145, -3.63021680109313, 57.6563047671537, -3.63022460937489, 57.65630545029, -3.63037109374983, 57.65630545029, -3.63051757812485, 57.65630545029, -3.63052538640662, 57.6563047671537, -3.63052639670317, 57.6563045770786, -3.63064846701559, 57.6562801630162, -3.63076982633862, 57.6562558911515, -3.63091559550656, 57.6562315962903, -3.630922422655, 57.6562299005905, -3.63106726873372, 57.6561816185641, -3.63116324963161, 57.6561576233396, -3.63116772306265, 57.6561562541272, -3.63117245320223, 57.6561542187173, -3.63131893757708, 57.6560809765297, -3.63141659382717, 57.6560321484047, -3.6314189674261, 57.6560308712871, -3.63142346403629, 57.6560279023819, -3.63152112028632, 57.6559546601945, -3.63161877653624, 57.6558814180069, -3.63171643278636, 57.6558081758195, -3.63172124896073, 57.6558039987736, -3.63181890521076, 57.6557063425235, -3.63191656146077, 57.6556086862735, -3.63201421771072, 57.6555110300235, -3.63201890027403, 57.6555055263302, -3.63202264077944, 57.6554993436399, -3.63207010412043, 57.6554044169579, -3.63411645017348, 57.6524079816659, -3.63456912555174, 57.6519791313074, -3.64234706604447, 57.6496140522234, -3.64234936368763, 57.6496132853771, -3.6423549038706, 57.6496108346436, -3.65094865387056, 57.6450942330811, -3.65095021742607, 57.6450933712871, -3.65095672677717, 57.6450888006725, -3.66042947177598, 57.6370564992496, -3.6604348366576, 57.6370511065912, -3.66043672357409, 57.6370486957131, -3.66556367669915, 57.6300174457131, -3.66556710690691, 57.630011949168, -3.66556971157965, 57.6300060167137, -3.6655714366409, 57.6299997715159, -3.66620620226587, 57.6268259433909, -3.66620706140031, 57.6268182498759, -3.66620658820841, 57.6268105230155, -3.66620479671508, 57.6268029918247, -3.66620174001804, 57.6267958795193, -3.66493220876805, 57.624427715457, -3.66492811590572, 57.6244214104963, -3.66492302992876, 57.6244158754362, -3.66491709296636, 57.6244112649558, -3.66491047092887, 57.6244077078966, -3.66288875241895, 57.6235309779987, -3.66288406300589, 57.6235288890039, -3.66288159025021, 57.6235278001856, -3.66287501881783, 57.6235260015337, -3.66287402037145, 57.6235257657724, -3.66056031150012, 57.6230630180061, -3.6581321621951, 57.622018913805, -3.64139066211811, 57.6036423109506, -3.64138632552834, 57.6036381475299, -3.64138000656868, 57.6036337106362, -3.63730280730977, 57.6012655171435, -3.63729565275014, 57.6012621751857, -3.6372921730507, 57.6012610665026, -3.6267941261757, 57.5983802071276, -3.62679003484413, 57.5983792871591, -3.62678422604128, 57.5983786484997, -3.61855668697879, 57.5980124375622, -3.61855468749991, 57.5980123930854, -3.61854742598842, 57.5980129832842, -3.61586160572836, 57.5984524828779, -3.61585376131205, 57.5984545579981, -3.61584837818913, 57.5984569265807, -3.61406615162662, 57.5993846609557, -3.61406019446767, 57.5993883802168, -3.61405488906942, 57.5993929816726, -3.61405036484433, 57.5993983530814, -3.61300056015679, 57.6008631968314, -3.61299775325639, 57.6008676408101, -3.61299548411242, 57.6008723819725, -3.61299378373008, 57.6008773555363, -3.61250550248008, 57.6026351680363, -3.61250454515874, 57.6026393946559, -3.61250387062665, 57.6026463233343, -3.61184468663706, 57.6363381693861, -3.6118453654712, 57.6363464174693, -3.61184606344506, 57.6363496680663, -3.61226077292213, 57.6379841112998, -3.61961829299983, 57.636216539, -3.63247636599985, 57.6356468770001, -3.63249432321282, 57.6356547715024, -3.63255987063195, 57.6356516144041, -3.64042505760611, 57.6391413482644, -3.64561926999983, 57.641424872, -3.63768469999985, 57.646226304, -3.63255774599983, 57.650946356, -3.62919778925394, 57.6559309078049, -3.62920209499975, 57.6559337456834, -3.62920893455916, 57.6559367692701, -3.62921618063231, 57.6559386175324, -3.62922363281245, 57.6559392393525, -3.6292294213055, 57.6559392393525, -3.62926507916404, 57.655974897211, -3.62927044458806, 57.6559794797161, -3.62927646081656, 57.6559831664657, -3.62928297971007, 57.6559858666798, -3.62928984075165, 57.6559875138701, -3.62929687499988, 57.6559880674775, -3.62932707755544, 57.6559880674775, -3.62936273541405, 57.656023725336)),3199);

SDO_GEOMETRY型は以下で定義されています。

CREATE TYPE sdo_geometry AS OBJECT (
 SDO_GTYPE NUMBER, 
 SDO_SRID NUMBER,
 SDO_POINT SDO_POINT_TYPE,
 SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY,
 SDO_ORDINATES SDO_ORDINATE_ARRAY);

SDO_GTYPE属性は、ジオメトリのタイプを表しています。前出のINSERT文では2003が指定されています。これは２次元ポリゴンを示しています。SDO_SRID属性は座標系(空間参照システム)を表します。4326は地理座標系です。SDO_ELEM_INFOによって、後続のSDO_ORDINATESに格納される座標の解釈方法が決まります。SDO_ELEM_INFO_ARRAY(1, 1003, 1)は、先頭の値から始まる - オフセットの無い（最初の１）、反時計回りで、頂点が直線セグメントに連結している単純なポリゴンであり、かつ、始点と終点が同じ点であることを要求しています(続く1003,1)。SDO_ORDINATES属性は、空間オブジェクトの境界を形成する座標の値を格納するNUMBER型の可変長配列です。

このように定義された領域に、ジオコーディングの結果として取得した座標が入らないことを検証するために、Oracle Spatialが提供している空間演算子SDO_ANYINTERACTを使用します。これは位相関係として以下に定義されているどれかに当てはまる場合にTRUEを返します。今回の場合はTRUEを返す行が存在する場合はドローンの飛行をリクエストしている住所が市街地に入るため、飛行を許可しない、ということになります。

以上の処理を実装するため、先ほどの動的アクション、ジオコード取得成功がTrueになったときに実行されるPL/SQLコードを以下に示すコードで置き換えます。

declare
    cursor c_geocoded_address
    is
        SELECT n002, n003
        from apex_collections where collection_name = 'GEOCODER_RESULTS'
        and n002 is not null and n003 is not null
        order by seq_id;
    r_geocoded_address c_geocoded_address%rowtype;
    lon varchar2(100);
    lat varchar2(100);
    result varchar2(100) := '';
    coordinates MDSYS.SDO_GEOMETRY; --Add
    overlap_count number; --Add
begin
    open c_geocoded_address;
    fetch c_geocoded_address into r_geocoded_address;
    lon := r_geocoded_address.n002;
    lat := r_geocoded_address.n003;
    close c_geocoded_address;
    if lon is not null and lat is not null then
        result := result || 'Address found: ' || '[' || lon || ',' || lat || ']';
        
        --Added code for urban area check
        coordinates := MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(lon, lat, NULL), NULL, NULL);
        SELECT COUNT(*) INTO overlap_count
        FROM URBAN_AREA_UK "t2"
        WHERE SDO_ANYINTERACT("t2"."GEOM", coordinates) = 'TRUE';
        if overlap_count = 0 then
            result := result || ' - ALLOWED';
        else
            result := result || ' - NO FLY ZONE';
        end if;
        --End of added code for urban area check
    else
      result := result || 'Address not found';
    end if;
    :P6_PERMIT := result;
exception
    when others then
        :P6_PERMIT := sqlerrm;
end;

ここで判定に使われているSQLは以下になります。

SELECT COUNT(*) INTO overlap_count
 FROM URBAN_AREA_UK "t2"
 WHERE SDO_ANYINTERACT("t2"."GEOM", coordinates) = 'TRUE';

overlap_countが0であれば（つまりSDO_ANYINTERACTの結果がTRUEになる行が存在しなければ)、ALLOWEDになります。

最後にいくつかフォームを開いて、許可(ALLOWED)または不許可(NO FLY ZONE)となる結果を確認してみましょう。

Requestor NameがIsaias Edwardsのリクエストは許可されました。

Ayden Odomのリクエストは許可されませんでした。

Oracle Spatialの機能をOracle APEXから使用するアプリケーションを作成するワークショップの内容は以上でした。Oracle Spatialが提供する機能の、ほんのサワリを使っただけですが、どのようにAPEXアプリケーションからOracle Spatialの機能を利用するか、感じがつかめたでしょうか。 加えて、Oracle APEXの方針声明に、新しいマップ・コンポーネントの提供が表明されていることから、今後はより地理空間情報の扱いが容易になることが期待されます。

Oracle Spatial and Graphとして、本当に多彩な機能が提供されていますし、すでにOracle Databaseを使用している場合、追加ライセンスの購入なしで利用可能になっています(こちらのブログ、またはライセンスガイドを参照のこと)。

最後にOracle Spatial概要説明資料を紹介します。とても興味深い内容ですよ。

Oracle Spatial 概要説明資料 from オラクルエンジニア通信

完

外部結合を含むビューを対話グリッドで扱う

外部結合を含むビューを対話グリッドで操作する方法について、開発者が解説をしていました。答えはプロセスの対話グリッド - 行の自動処理(DML)のターゲット・タイプをPL/SQL Codeにして、INSERT/UPDATE/DELETE操作を実際に行うコードを書く、でした。

私はビューにINSTEAD OFトリガーを付ければ、INSERT/UPDATE/DELETE操作を(表に行うのと同様に)実行できるので、それで出来ると思っていたのですが、実際やってみたところ、うまく行きませんでした。役に立つ情報かもしれないので、うまく行かなかった方法とうまく行く方法を以下に説明します。

例として使う表とビュー

ID列とNAME列(名前)を持つ表TST_PERSONSと、同じくID列とDOMICILE列(本籍)を持つ、表TST_PERSON_DOMICILESがあるとします。表の定義は以下です。

CREATE TABLE  "TST_PERSONS" 
   ( "ID" NUMBER, 
 "NAME" VARCHAR2(80), 
  PRIMARY KEY ("ID")
  USING INDEX  ENABLE
   )
/

CREATE TABLE  "TST_PERSON_DOMICILES" 
   ( "ID" NUMBER, 
 "DOMICILE" VARCHAR2(80), 
  PRIMARY KEY ("ID")
  USING INDEX  ENABLE
   )
/

そして、この２つの表を結合してビューTST_PERSONS_Vとします。DOMICILEは必ずしもすべてのTST_PERSONS表の行に紐づく行があるとは限らないため、外部結合(left outer join)としています。

CREATE OR REPLACE FORCE VIEW "TST_PERSONS_V" ("ID", "NAME", "DOMICILE") AS 
  select p.id, p.name, d.domicile 
from tst_persons p left outer join tst_person_domiciles d 
  on p.id = d.id
/

このビューを、対話グリッドによって表と同様に操作する、というのが課題です。

INSTEAD OFトリガー

まず最初にINSERT/UPDATE/DELTE操作を行うINSTEAD OFトリガーを書きました。以下のトリガーになります。IDの値が与えられていない場合、TST_PERSONS_SEQシーケンスから自動的に採番するようにしています。

create or replace trigger tst_persons_v_trig
instead of insert or update or delete on tst_persons_v
for each row
declare
  l_id number;
begin
  if deleting then
    delete from tst_person_domiciles where id = :old.id;
    delete from tst_persons where id = :old.id;
  elsif inserting then
    if :new.id is null then
      l_id := tst_persons_seq.nextval;
    else
      l_id := :new.id;
    end if;
    insert into tst_persons(id, name) values(l_id, :new.name);
    if :new.domicile is not null then
      insert into tst_person_domiciles(id, domicile) values(l_id, :new.domicile);
    end if;
  elsif updating then
    update tst_persons set name = :new.name where id = :old.id;
    merge into tst_person_domiciles d
    using (select :old.id as id, :new.domicile as domicile from dual) n
    on (d.id = n.id)
    when matched then
    update set d.domicile = n.domicile
    when not matched then
    insert (id, domicile) values(n.id, n.domicile);
  end if;
end;

対話グリッドを作成する - その１

作成したビューを指定して対話グリッドを作成しました。ROWIDはないので、主キー列としてIDを選択しています。

作成した対話グリッドを操作すると、更新(UPDATE)、削除(DELETE)は問題ありませんが、行の追加(INSERT)を行うとORA-22816: RETURNING句ではサポートされていない機能です。とエラーが通知されます。ビューを対象にしたSQLにRETURNING句を付けられないことが原因です。

このエラーを回避するため、対話グリッドのデータベース操作を行うプロセスの設定に含まれる挿入後に主キーを返すをOFFにします。

エラーは回避され、ビューに対する操作は出来るようになりましたが、行の追加を行った後、保存をクリックすると対話グリッド上から追加した行が消えてしまいます。画面をリロードすると表示されるため、データベースに対する操作は行われています。

この動作は外部結合を持つビューがソースになっていることが理由ではなく、挿入後に主キーを返すをOFFにした場合の既定の動作です。単一の表であっても、この指定をOFFにすると同じ動作になります。

動的アクションを追加する

毎回リロードするのを回避するため、動的アクションを追加することにしました。対話グリッドには色々なイベントが定義されていますが、その中の保存[対話グリッド]というイベントで、対話グリッドのリフレッシュを行うアクションを設定します。

保存を実行すると動的アクションが実行され、データがリフレッシュされるようにはなりましたが、新たに入力した行が同じ位置に止まらず、最終行になってしまいます。また、更新、削除でも動的アクションが起動し、対話グリッドのリフレッシュが実行されてしまいます。

主キーの値を指定する

ビューではROWIDが取れないため、ユニークな値であるIDをTST_PERSONS_Vビューの主キーとして設定していますが、主キーについて、トリガーによる自動採番やOracle 12cから使用できるIdentity Columnを使っていないケースはあります。むしろ、そちらの方が多いでしょう。作成しているトリガーのinsertingに記述したコードでは、外部からIDが与えられる場合は、そちらの値を使用するように記述してあります。ですので、作成した対話グリッドのIDを非表示からテキスト・フィールドに変更し、入力可能にしてみます。

行の追加をクリックすると空白行が挿入されますが、IDにはt1002が設定されています。これは対話グリッドが認識しているユニークなカラムに自動的に設定される値で、カラムのデフォルトや動的アクションを使用しても変更することはできません。入力されたt1002といった値を手作業で変更すれば、変更したデータが保存されますが、それは期待している動作ではありません。

対話グリッドに与えるユニークな列(ROWIDまたは主キー列)は、必ず非表示で、かつ、自動的に生成される値である必要があります。

ROWIDをビューに追加する

TST_PERSONS表には必ず行が存在するので、そのROWIDをビューから見えるように新たなビューTST_PERSONS_R_Vを以下のように定義します。

CREATE OR REPLACE FORCE VIEW "TST_PERSONS_R_V" ("RID", "ID", "NAME", "DOMICILE") AS 
  select p.rowid as rid, p.id, p.name, d.domicile 
from tst_persons p left outer join tst_person_domiciles d 
  on p.id = d.id
/

ROWIDは予約語であるため、ビューからはROWIDの代わりにRIDとしてTST_PERSONS表のROWIDを参照できるようにしています。ID自体が主キーであることには変わりなく、またNOT NULLの列であるため、INSTEAD OFトリガーは対象とするビューを変えるだけで使用できます。ビューのトリガーはビュー定義が変わる（CREATE OR REPLACE VIEWが実行される)と無くなってしまうので、注意しましょう。せっかく記述したトリガー、それも結構な量のコードが無くなっていると気が遠くなります。

定義を変えて作成したビューTST_PERSONS_R_Vを操作する対話グリッドのページを作成します。主キー列としてRIDを指定します。

先ほどの対話グリッドと同様に、プロセスの挿入後に主キーを返すをOFFにして、エラーの発生を回避します。RIDを非表示のユニーク列として扱うことにより、ID列の入力が可能になりました。また列の値のデフォルトを設定すると、行の追加を行った時点で値が設定されます。

上記では、ID列のデフォルトとして、タイプをPL/SQLファンクション本体、その内容としてreturn tst_persons_seq.nextvalを設定しています。タイプとしてSQL問合わせを選んでselect tst_persons_seq.nextval from dualを設定しても同じデフォルト値が設定されます。なぜか、タイプを順序として、TST_PERSONS_SEQを指定してもデフォルトの値が設定されません。ワークアラウンドはありますので、特には困らないのですが、困らないから誰も気にしていないのかもしれません。

期待通りに動作する設定

期待通りに動作する対話グリッドの設定の手順を示します。まず、対話グリッドを作成する際のソースの定義です。ビューではなくSQLを入力しています。INSTEAD OFトリガーを作る必要が無くなるため、ビューの作成は必須ではありません。問い合わせ自体は、ビューを定義したSQLと同じです。

select p.rowid rid, p.id, p.name, d.domicile 
from tst_persons p left outer join tst_person_domiciles d 
on p.id = d.id

対話グリッドがリージョンとして配置されたページが作成されたら、左ペインでプロセス・ビューを表示させてプロセス - 対話グリッド・データの保存を選択します。プロパティ・エディタからプロセスの設定に含まれるターゲット・タイプをPL/SQL Codeに設定し、挿入/更新/削除するPL/SQLコードを記述します。

記述したコードは以下です。ほとんどINSTEAD OFトリガーと同じです。

begin
  case :APEX$ROW_STATUS
  when 'C' then
    if :ID is null then
      :ID := tst_persons_seq.nextval;
    end if;
    insert into tst_persons(id, name) values(:ID, :NAME)
    returning rowid into :ROWID;
    if :DOMICILE is not null then
      insert into tst_person_domiciles(id, domicile) values(:ID, :DOMICILE);
    end if;
  when 'U' then
    update tst_persons set name = :NAME where id = :ID;
    merge into tst_person_domiciles d
    using (select :ID as id, :DOMICILE as domicile from dual) n
    on (d.id = n.id)
    when matched then
    update set d.domicile = n.domicile
    when not matched then
    insert (id, domicile) values(n.id, n.domicile);
  when 'D' then
    delete from tst_person_domiciles where id = :ID;
    delete from tst_persons where id = :ID;
  end case;
end;

行の追加を行ったときに、そのまま行が残るようにするための部分が以下になります。

insert into tst_persons(id, name) values(:ID, :NAME)  returning rowid into :ROWID;

対話グリッド側から渡される値、ID、NAMEおよびDOMICILEを参照するだけではなく、対話グリッドで主キーとして扱う値、ここではROWIDをプロセスの中で設定します。これで実質的に、挿入後に主キーを返すをONに設定したことになります。

作成された対話グリッドにはROWIDの項目が追加されています。しかし、このビューにはROWIDはなく、RID列としてその値を参照できるようにしています。ですので、ROWIDをRIDに紐付けます。レンダリング・ビューから対話グリッドのリージョンを探し、その列に含まれるROWIDを選択します。そのソースのデータベース列として設定されているROWIDをRIDに変更して保存します。

ID列のデフォルトも設定しておきます。設定内容は説明済みですが、既存の値の重複コピーはOFFにします。主キーなので、値が一意である必要があるためです。

これで、単一の表を基に対話グリッドを作成した場合と同じ動作をするようにできました。今回はビューを対象にしていますが、色々な応用ができると思います。

完

sentryファンクションによる外部ユーザー認証

Oracle APEXの開発メンバーの一人、Christian Neumuellerによるこちらの記事に記載されている、外部の認証をOracle APEXに引き継ぐ方法を紹介します。この記事ではOracle Formsとの連携を意識していますが、例ではsqlplusを使用しています。Oracle FormsやOracle E-Business Suiteが稼働している環境が手元にある方はそれほど多くはいないはずなので、適切な選択でしょう。ユーザー認証の実装にはAPEX_JWTパッケージと、カスタム認証スキームのsentryファンクションを使用しています。

以前に認証と認可の実装を学ぶというテーマでOracle APEXが持っている認証と認可を行う仕組みについて勉強会を実施しました。資料はこちらのスライドになります。このときは監視ファンクション(sentryファンクション)について、ほとんど説明をしませんでした。このファンクションはセッション管理およびユーザー認証を自力で実装する際に使用します。今まで参照した情報で、一番詳しくて、かつ、わかりやすい説明はMenno Hoogendijk（当時はオランダのQualogyというところに勤めていましたが、現在はオラクルに所属しているOracle APEXのプロダクト・マネージャーです)によるExploring the details of APEX sessionsだと思います。詳しく理解したい場合には、お勧めの資料です。

では、sentryファンクションから始めましょう。辞書からは、歩哨とか見張り、という意味になっていますが、Oracle APEXでsentryファンクションが行うことは、受け取ったHTTPリクエストが有効なセッションに含まれるかどうかを真偽値で返すことです。trueであればセッションが有効ということでページの処理に入ります。有効なセッションがなければ新規にセッションが作成されます。falseであればセッションは有効ではないので、ページの処理には入らず、ユーザー認証を要求するかページへのアクセスを拒否します。

sentryファンクションの動作を理解するために、つねにtrueを返すファンクションを定義してみます。共有コンポーネントの認証スキームを開いて、新たに認証スキームを作成します。認証スキームの作成ウィザードが開いたら、スキームの作成としてギャラリからの事前構成スキームに基づくを選択します。認証スキームの設定が開いたら、名前を設定し(ここではalways true)、スキームタイプはカスタム、監視ファンクション名としてsentry、そして以下のコードをソースのPL/SQLコードとして指定します。設定した後、認証スキームの作成をクリックすると、認証スキームが新規に作成され、それがカレント・スキームに設定されます。

function sentry
return boolean
is
begin
  return true;
end;

この認証スキームを実装したアプリケーションは、ユーザー認証なしでアクセスできます。また、有効なセッションが無い(URLに有効なセッションIDが含まれない)場合は、つねに新しいセッションが作成されます。ユーザー認証を行っていないため、ユーザー名はnobodyになっています。

次に、sentryファンクションが必ずfalseを返す認証スキームを作成し、カレント・スキームにします。作り方は先ほどと同じです。

この状態でアプリケーションを実行すると、sentryファンクションが必ずfalseを返すため、標準のユーザー認証画面が表示されます。ただし、どのようなユーザー名やパスワードを入力しても、そのユーザーが認証されることはありません。sentryファンクションは、ユーザー認証されたかどうかを判断してtrueを返すコードを含まず、必ずfalseを返すためです。

標準のログイン・フォームと違うフォームを使用する、または、標準をカスタマイズするには、ユーザー・インターフェースの詳細の属性に含まれるログインURLとして指定されているページを置き換えるか、指定されているページを変更します。

フォームという仕組み自体を置き換えるには、セッション・プロシージャ名が無効ですを設定します。例えば、ヘルプをクリックして表示されるプロシージャーinvalid_session_basic_authを設定してみます。

ログインのフォームの代わりに、HTTPのベーシック認証を要求されるようになります。

この他にも、ユーザー認証にまつわるカスタマイズを実装する色々な仕掛けがありますが、それらを最初に制御するのはsentryファンクションです。ここでtrueと返せばアプリケーションにアクセスは許可されますし、falseを返せばアクセスは禁止されます。ですので、sentryファンクションに記載されるロジックにて、ユーザーがどのように認証されるかを決めることができます。Oracle APEXが標準で提供している機能をほぼ使用せず、最も自由度の高い実装を可能にしますが、生産性という意味では一番低くなります。なので、本当に必要でなければ、sentryファンクションは使わない方がよいです。

さて、最初に紹介した、こちらの記事ですが、JWT(JSON Web Token)を作成し、それを渡すことで認証情報の引き継ぎを行っています。JWTを受け取ったOracle APEXのアプリケーションでは、sentryファンクション内で受け取ったJWTの検証を実施しています。

IDトークン(JWT)が問い合わせ変数X01として渡されることを前提として記述されたsentryファンクションが以下になります。元のコードから認証と関係していない部分を削除し、コメントを日本語で付加しています。問い合わせ変数のX01はOracle APEXのAjaxコールの際に使われることが想定されており、これはチェックサムによる保護の対象になっていないため、IDトークンの受け渡しとして使用しています。電子署名(正確にはHMAC)の検証を行うために使用している鍵情報として"sharedkey!"を使っています。この鍵はJWTの発行者と対象の間のみで共有します。

function sentry
    return boolean
is
    l_x01      varchar2(32767);
    l_jwt      apex_jwt.t_token;
    l_jwt_user varchar2(255);
begin
    /* 問い合わせパラメータX01としてJWTが渡される。 */
    l_x01 := v('APP_AJAX_X01');
    apex_debug.trace('X01=%s_x01');
    if l_x01 like '%.%.%' then -- header.payload.signatureのフォーマット確認
        begin
            /* JWTをデコードする。HMACの検証も行う */
            l_jwt := apex_jwt.decode (
                         p_value         => l_x01,
                         p_signature_key => sys.utl_raw.cast_to_raw('sharedkey!') );
            apex_debug.trace('JWTload=%s', l_jwt.payload);
            /* IDトークンの発行者(iss)、対象(aud)および有効期限の検証 */
            apex_jwt.validate (
                 p_token => l_jwt,
                 p_iss   => 'sqlplus',
                 p_aud   => 'APEX' );
            apex_debug.trace('...validated');
            /* JWTをパースし、ユーザーの一意識別子(sub)を取り出す */
            apex_json.parse (
                 p_source => l_jwt.payload );
            l_jwt_user := apex_json.get_varchar2('sub');
        exception when others then
            apex_debug.trace('...error: %s', sqlerrm);
        end;
    end if;
    /* 
     * JWTがX01で渡されている場合は、認証が要求されている
     * セッションが継続していることを前提にした場合、X01にJWTは渡されないため、
     * すぐに以下の処理が呼び出される。
     */
    if apex_authentication.is_public_user then -- セッションがパブリック・ユーザーだから未ログイン
        if l_jwt_user is not null then -- JWTを受け取って、すでにユーザー名が取り出されている
            apex_authentication.post_login (
                p_username => l_jwt_user ); -- JWTに含まれるユーザーをAPP_USERとする
        else
            return false; -- JWTを受け取っていないので、拒否
        end if;
    elsif apex_application.g_user <> l_jwt_user then -- 認証済みユーザーとJWTのユーザーが異なる
        apex_debug.trace('...login user %s does not match JWT user %s',
            apex_application.g_user,
            l_jwt_user );
        return false; -- 違うユーザーなので拒否
    end if;
    return true; -- セッションを継続
end sentry;

このsentryファンクションで認証されるURLを生成するためのPL/SQLコードが以下になります。ホスト名やベースとなるURLは、それぞれの環境によって異なるでしょう。http://localhost:8080/ords/xepdb1/f?p=101:1の部分です。URLに含まれるLEVEL9の部分は必須ではありませんが、デバッグ・レベルを9 - TRACEのレベルまで引き上げることで、sentryファンクションのデバッグを行えるようにしています。

set define off
set serveroutput on
declare
  l_jwt varchar2(32767);
begin
  l_jwt := apex_jwt.encode (
     p_iss           => 'sqlplus',
     p_aud           => 'APEX',
     p_sub           => 'TESTUSER',
     p_exp_sec       => 10,
     p_signature_key => sys.utl_raw.cast_to_raw('sharedkey!') );
 sys.dbms_output.put_line (
        'http://localhost:8080/ords/xepdb1/f?p=101:1:::LEVEL9:&x01='||l_jwt);
end;
/
exit;

印刷されたURLでアプリケーションにアクセスしたときに、ユーザー名がtestuserとして表示されれば、sentryファンクションが正しく機能しています。

p_exp_secとして10が設定されていますから、ここで生成されたトークンが有効なのは生成後１０秒までです。

完