日日是Oracle APEX: 熊の出没を報告するアプリを作成する

熊の出没を報告するAPEXアプリケーションを試作してみました。入力の手間を少なくすることを、第一の目標にしています。以下の機能を実装しています。

ブラウザから取得できる現在位置の座標から住所を取得する。
写真のアップロードができる。
状況は音声で入力する。
OpenAI WhisperのAPIを呼び出して、音声から文字起こしをする。
OpenAI ChatGPTのAPIを呼び出して、状況を説明した文章からファセット検索に使う単語のJSON配列を生成する。

1の現在位置から住所を求めるために、こちらの記事「国土交通省のデータを使って簡易的な逆ジオコーディングを実装する」の実装を使っています。また、4の音声のレコーディングには、こちらの記事「OpenAI Whisperを使った文字起こしアプリの作成(5) - ブラウザからの音声入力」の実装を使っています。OpenAIのAPIを呼び出すために使用するWeb資格証明の作成方法については、こちらの記事「OpenAIのChatGPTのAPIを呼び出すAPEXアプリを作る」で紹介しています。

OpenAIのWhisperとChatGPTのAPI呼び出しは、アカウント作成時に与えられる$40のクレジットを使い切る（もしくは期限切れになる）と、使用量に応じて課金されます。

今回作成するアプリケーションは以下のように動作します。

以下より、アプリケーションの作成手順を紹介します。

最初に、クイックSQLの以下のモデルを元に、出没情報を保存する表を作成します。表BEAR2_SIGHTINGSには出没情報、表BEAR2_PHOTOSには、出没情報に紐づく写真、表BEAR2_VOICESには、音声による報告が保存されます。

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

SQLスクリプトの編集画面が開くので、そのまま実行します。確認画面が開くので、即時実行をクリックします。その後、生成されたDDLが実行されて上記の３つの表が作成されます。

この画面からはアプリケーションの作成は行いません。

アプリケーション作成ウィザードを起動し、空のアプリケーションを作成します。アプリケーションの名前は熊出没情報とします。スマートフォンにインストールできるように、機能のプログレッシブWebアプリケーションのインストールにチェックを入れます。

アプリケーションの作成をクリックします。

アプリケーションが作成されます。

報告した内容を表示するページを作成します。ページの作成をクリックし、ページ作成ウィザードを起動します。

クラシック・レポートを選択します。

ページ番号は2、名前は報告内容とします。データ・ソースの表/ビューの名前としてBEAR2_SIGHTINGSを指定します。このページは、出没情報の送信後に遷移する画面なので、このページを直接開くことはありません。そのため、ナビゲーションのブレッドクラムの使用、ナビゲーションの使用ともにオフにします。

ページが作成されます。

このページは登録された１件の出没情報だけを表示します。表BEAR2_SIGHTINGSで表示対象とする行の主キーの値を保持するページ・アイテムを作成します。

識別の名前はP2_ID、タイプは非表示とします。

クラシック・レポートのリージョン報告内容を選択し、ソースのWHERE句に以下を記述し、表示する対象を１つに絞ります。

id = :P2_ID

プロパティ・エディタの属性タブを開き、外観のテンプレートをValue Attribute Pair - Columnに変更します。横並びだった列を縦方向に並べます。

出没情報に紐づけられた写真を表示するために、カード・リージョンを作成します。

識別のタイトルは写真、タイプはカードを選択します。ソースの表名としてBEAR2_PHOTOSを指定し、WHERE句に以下を記述します。

sighting_id = :P2_ID

プロパティ・エディタの属性タブを開きます。

今回は写真が表示される最低限の変更のみを実施します。メディアのソースとしてBLOB列を選び、BLOB列としてPHOTOを選択します。その後にカードの主キー列1にIDを指定します。

変更を保存します。

報告内容を表示するページは、以上で完成です。

熊出没情報を入力するフォームのページを作成します。

＋メニューよりページを実行し、ページ作成ウィザードを開始します。

フォームを選択します。

ページ番号は3、名前は熊出没報告とします。データ・ソースの表/ビューの名前としてBEAR2_SIGHTINGSを指定します。このページも直接開くことはない（現在位置の座標を受け取るため、つねにホーム・ページからリダイレクトされます）ため、ナビゲーションのブレッドクラムの使用、ナビゲーションの使用ともにオフとします。

次へ進みます。

主キー列1としてID (Number)を選択します。ブランチ・ページの送信時にここにブランチは、先ほど作成した報告内容を表示するページを遷移先とするため2を指定します。取り消してページに移動も2としますが、取消ボタンは削除するため、取り消し処理が行われることはありません。

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

ページが作成されます。

ページ・プロパティの識別の別名をreportに変更します。ホーム・ページからリダイレクトする宛先として、この別名を使用します。外観のページ・テンプレートとしてMinimal(No Navigation)を選択し、不要な装飾を除きます。保存されていない変更の警告はオフにします。

セキュリティのページ・アクセス保護を、引数にチェックサムが必要から制限なしに変更します。ホーム・ページからリダイレクトする際に、チェックサムの計算を省略しているためです。

このページが日本時間で動作するように、レンダリング前/ヘッダーの前にプロセスを作成し、タイムゾーンを設定します。

識別の名前は日本時間を強制、タイプはコードの実行を選択します。ソースのPL/SQLコードとして以下を記述し、タイムゾーンをAsia/Tokyoに設定します。

apex_util.set_session_time_zone('Asia/Tokyo');

ページ・アイテムP3_VOICE_IDのタイプを非表示に変更し、設定の保護された値はオフにします。この値は、音声ファイルのアップロードを行った後に、JavaScriptのコードから設定されます。

ページ・アイテムP3_REPORT_DATEを選択します。

ラベルを出没日時とします。設定の時間の表示をオン、外観の書式マスクをYYYY-MM-DD HH24:MI:SS、デフォルトのタイプとして式を選択し、PL/SQL式に以下を記述します。

to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS')

出没日時の初期値として現在の時刻を設定しています。

ページ・アイテムP3_LATITUDE、P3_LONGITUDEを選択します。これらの座標値は地図上をクリックして設定します。ユーザーが入力することはないので、タイプは非表示にします。JavaScriptから値を設定するため、保護された値はオフにします。セッション・ステートのストレージとしてセッションごと(永続)に変更します。

ページ・アイテムP3_ADDRESSを選択します。このページ・アイテムは逆ジオコーディングを行った住所を、選択リストにします。

タイプを選択リストに変更し、ラベルは出没した場所とします。LOVのタイプをSQL問合せ、SQL問合せとして以下を記述します。

追加値の表示はオフ、NULL値の表示もオフです。カスケードLOVの親アイテムにP3_LATITUDE、P3_LONGITUDEを設定し、これらの値が変更されたときに、選択リストがその座標に近い住所に更新されるようにします。

ページ・アイテムP3_REPORT_TEXT、P3_ATTRIBUTES、P3_CREATED、P3_CREATED_BY、P3_UPDATED、P3_UPDATED_BYは、APEXのプロセスやトリガーによりサーバー側で設定されます。ユーザーが扱うことはありません。

これらのページ・アイテムをすべて選択し、構成のビルド・オプションを使ってコメント・アウトします。

フォームのリージョン熊出没注意を選択します。

外観のテンプレートをBlank with Attributesに変更し、見かけをシンプルにします。

プロパティ・エディタの属性タブを開きます。

編集の実行可能な操作から行の更新と行の削除のチェックを外します。この後、ボタンSAVE、DELETE、CANCELを削除しますが、ボタンを削除してもJavaScriptコンソールからコードを実行すると編集や削除を呼び出すことができます。この設定により、そのようなリスクからデータを守ることができます。

ボタンCANCEL、DELETE、SAVEを選択し、コンテキスト・メニューを表示させて削除を実行します。

ボタンCREATEを選択し、ページ・アイテムP3_UPDATED_BYの下に配置します。

識別のラベルを報告に変更し、動作のアクションを動的アクションで定義に変更します。

外観のテンプレート・オプションを開き、詳細のWidthをStretch、Spacing TopをLargeに変更します。

地図を表示するリージョンをRegion Bodyに作成します。

Region Body上でコンテキスト・メニューを表示し、リージョンの作成を実行します。

作成したリージョンをページ・アイテムP3_ADDRESSの下に配置します。識別のタイトルは地図、タイプはマップにします。外観のテンプレートをBlank with Attributes、詳細の静的IDとしてmapを設定します。

レイヤーを選択します。

識別の名前は現在位置とします。ソースのタイプをSQL問合せに変更し、SQL問合せとして以下を記述します。

select
    to_number(:P3_LATITUDE) as lat
    ,to_number(:P3_LONGITUDE) as lon
from dual

送信するページ・アイテムとしてP3_LATITUDE、P3_LONGITUDEを指定します。

列のマッピングのジオメトリ列のデータ型として経度/緯度を選択し、経度列にLON、緯度列にLATを選択します。

マップ・リージョン地図に動的アクションを作成します。

識別の名前を地図上をクリックとします。タイミングのイベントとしてマップがクリックされました[マップ]を選択します。

TRUEアクションをJavaScriptコードの実行に変更します。設定のコードとして以下を記述します。

apex.items.P3_LATITUDE.setValue(this.data.lat);
apex.items.P3_LONGITUDE.setValue(this.data.lng);
apex.region("map").refresh();

リージョン地図を選択し、プロパティ・エディタの属性タブを開きます。

マップの高さを240に変更します。初期位置およびズームのタイプをSQL問合せに変更し、SQL問合せとして以下を記述します。ホーム・ページから熊出没報告へ遷移する際にページ・アイテムP3_LATITUDEとP3_LONGITUDEに現在位置の座標値が渡されます。そのため、地図が初期化される際の初期位置は現在位置、ズーム・レベルは12になります。

select
    to_number(:P3_LATITUDE) as lat
    ,to_number(:P3_LONGITUDE) as lon
    ,12 zoom
from dual

ジオメトリ列のデータ型として経度/緯度を選択します。初期経度列はLON、初期緯度列はLAT、初期ズーム・レベルはZOOMとします。

レイヤーは１つだけなので、凡例の表示はオフにします。

写真を選択するページ・アイテムを作成します。作成したページ・アイテムはリージョン地図の下に配置します。

識別の名前はP3_PHOTOS、タイプとしてファイル参照...を選択します。ラベルは写真とします。

設定の表示形式にNative File Browseを選択し、モバイル・デバイスでの最適な表示形式を優先します。記憶域タイプはTable APEX_APPLICATION_TEMP_FILES、ファイルをパージするタイミングとしてEnd of Requestを選択します。アップロードした写真ファイルは表BEAR2_PHOTOSに保存するため、一時表に残しておく必要はありません。複数ファイルの許可をオンにし、複数のファイルを一度にアップロード可能にします。ファイル・タイプとしてimage/*を指定し、アップロード対象をイメージに限定します。最大ファイル・サイズは10000KB(=10MB)とします。

セッション・ステートのストレージはリスエストごと(メモリーのみ)を設定します。

音声を録音するためのプレーヤーを配置します。

ページ・アイテムP3_PHOTOS上でコンテキスト・メニューを表示させ、下にリージョンを作成を実行します。

作成したリージョンの識別のタイトルを録音とします。タイプは静的コンテンツ、ソースのHTMLコードとして以下を記述します。

<audio id="player" controls></audio>

外観のテンプレートとしてBlank with Attributesを選択します。

リージョン録音上でコンテキスト・メニューを表示させ、ボタンの作成を実行します。

作成したボタンのボタン名をSTART、ラベルを録音開始とします。

レイアウトの位置はPrevious、動作のアクションとして動的アクションで定義を指定し、詳細のカスタム属性として以下を記述します。

data-action="#action$start-audio-recording"

同様の手順でもう一つボタンを作成します。ボタン名をSTOP、ラベルを録音終了とします。

レイアウトの位置はNext、動作のアクションとして動的アクションで定義を指定し、詳細のカスタム属性として以下を記述します。

data-action="#action$stop-audio-recording"

ページ・プロパティのJavaScriptのファンクションおよびグローバル変数の宣言に以下を記述します。残念なことに、このコードではiOS上のSafariでは録音が機能しません。AudioContextを使うとiOSのSafariでもWeb Audio APIが使えるとの情報もありますが、本題から外れるので今回は対応していません。

音声データのアップロードを受け付けて、表BEAR2_VOICESに保存するRESTサービスを作成します。

SQLワークショップからRESTfulサービスを開きます。

モジュールとしてuploadを作成し、そのモジュールにテンプレートとしてvoiceを作成します。voiceのPOSTハンドラとして、ソースに以下のPL/SQLコードを記述します。送信されたファイルを受け付けて表BEAR2_VOICESに保存します。音声ファイルを保存した行の主キーの値を呼び出し元に返します。この返却されたIDは、ページ・アイテムP3_VOICE_IDに設定されます。

本来であればApex-Sessionヘッダーなどを使ってRESTサービスを保護する必要がありますが、今回の手順では省略しています。保護の方法については、こちらの記事を参照してください。

完全なURLのホスト名以下のパスは、APEXから音声ファイルのアップロードを行う際にURLとして指定します。メモを取っておいてください。

APEXのページ・デザイナに戻ります。

ボタンCREATEに動的アクションを作成します。

識別の名前は録音を保存してから送信とします。タイミングのイベントはボタンのデフォルトであるクリックになります。

TRUEアクションはJavaScriptコードの実行に変更し、設定のコードに以下を記述します。最初に録音データをアップロードし、その後にページの送信を行います。コード中のrequest.openに与えるURLは環境に合わせて変更します。

左ペインでプロセス・ビューを開き、アップロードされた写真ファイルを保存するプロセスを作成します。

新規にプロセスを作成し、プロセスプロセス・フォーム熊出没報告の下に配置します。識別の名前は写真を保存、タイプはコードの実行です。ソースのPL/SQLコードに以下を記述します。

サーバー側の条件のボタン押下時にCREATEを指定します。

アップロードされた音声ファイルを、OpenAIのWhisperを呼び出して文字起こしを行い、その文字列からファセット検索に使用する単語を抽出するために、ChatGPTのAPIを呼び出します。ChatGPTのモデルとしてgpt-3.5-turboを指定しています。プロンプトをもっとチューニングすると、より適切な結果が得られると思います。

以下のコードを実行し、パッケージUTL_BEAR_OPENAIを作成します。SQLワークショップのSQLスクリプトを使って実行できます。

上記のパッケージに含まれるプロシージャを呼び出すプロセスを作成します。

新規にプロセスを作成します。作成したプロセスは写真を保存の下に配置します。

識別の名前はWhisperとChatGPT呼び出し、タイプはコードを実行です。ソースのPL/SQLコードに以下を記述します。

サーバー側の条件のボタン押下時としてCREATEを設定します。

プロセスプロセス・フォーム熊出没報告を選択します。成功メッセージに「目撃報告が送信されました。」を設定し、デフォルトのメッセージを変更します。

ブランチページに移動２を選択し、動作のターゲットを開きます。

アイテムの設定として、名前がP2_ID、値が&P3_ID.を追加します。今回作成された出没情報のIDの値が&P3_ID.なので、その値がページ・アイテムP2_IDに渡されます。結果として、今回作成された出没情報が遷移先のページで表示されます。

ページ・デザイナでホーム・ページを開きます。

ページ・プロパティの外観のページ・テンプレートをMinimal(No Navigation)に変更します。ホーム・ページはアプリケーションにサインインした直後に開きますが、現在地の座標を取得した後すぐにページ番号３の熊出没報告のページに移動します。そのため、ナビゲーションは不要です。