Oracle APEX 21.1では新たにデータをロードするAPIとして、APEX_DATA_LOADINGパッケージが追加されました。このパッケージに含まれるLOAD_DATAファンクションを使って、データをロードするアプリケーションを作ってみます。
データや定義には、以前の記事を流用します。
APEX_DATA_LOADINGを使うアプリケーションの準備
最初にクイックSQLのモデルより、表FDL_CITYLISTおよびFDL_FILESを作成します。以前の記事と同じ名前にすると、表が存在するというエラーが発生するかもしれません。そのため、プレフィックスをFUPからFDLに変更しています。
# prefix: fdl
# semantics: default
citylist
prefecture vc80
city vc80
count num
files
content file
SQLワークショップのユーティリティより、クイックSQLを開き、モデルの記述を行います。その後、SQLの生成、SQLスクリプトを保存、レビューおよび実行を順次実行します。
SQLのレビュー画面から実行します。
表が作成されたことを確認し、アプリケーションの作成を実行します。
確認ダイアログからアプリケーションの作成を実行します。
アプリケーション作成ウィザードが開くので、名前にAPEX_DATA_LOADINGサンプルと入力し、アプリケーションの作成を実行します。
アプリケーションが作成されたら、ページ番号4のFilesのページを開きます。ページの別名はfilesからfilelistに変更しておきます。(原因は分かりませんが、ページの別名がfilesのままだとHTTPエラー400 Bad Requestが発生しました。)
表FDL_FILESの内容を一覧するレポートが表示されます。
アクション・メニューの列より、全ての列をレポートに表示するように設定を変更します。適用をクリックします。
Contentにcitylist.xlsxを選択し、作成をクリックします。
ファイルのアップロードに関する設定が不足しているので、追加します。
ページ・デザイナにて、ページ番号5の表FDL_FILESを操作するフォームを開きます。左ペインのレンダリング・ビューより、ページ・アイテムP5_CONTENTを選択し、右ペインのプロパティ・エディタにてMIMEタイプ列にCONTENT_MIMETYPE、ファイル名列にCONTENT_FILENAME、文字セット列にCONTENT_CHARSET、BLOB最終更新列にCONTENT_LASTUPDを設定します。
変更を保存し、再度citylist.xlsxをアップロードします。今度はファイル名(Content Filename)、最終更新時刻(Content Lastupd)、MIMEタイプ(Content Mimetype)も表示されます。
アップロードされたファイルをAPIを使用して取り込むための、データ・ロード定義を作成します。
共有コンポーネントのデータ・ロード定義を開きます。
作成を実行します。
データ・ロードの作成として最初からを選択し、次へ進みます。
名前としてCITYLISTを入力します。ターゲット・タイプは表、表名としてFDL_CITYLISTを選択し、次へ進みます。
ソース・タイプとしてファイルのアップロードを選択し、サンプル・ファイルにcitylist.xlsxを与えます。次へ進みます。
列見出しの最初の行にヘッダーが含まれるにチェックが入っていることを確認します。ソース列とマップ先となる表の列名は一致しているので、自動的に検知された設定のまま、変更は不要です。ページの作成および追加をクリックします。
ページの名前にデータのロードを入力し、次へ進みます。
ナビゲーションのプリファレンスとして、新規ナビゲーション・メニュー・エントリの作成を選択します。次へ進みます。
データ・ロードはCITYLIST、データのアップロード元はファイル、最大ファイル・サイズ(MB)は５と設定されています。変更は行わず、ページの作成を実行します。
データ・ロード定義と、その定義を使用した、データをロードするページが作成されます。
以上で、パッケージAPEX_DATA_LOADINGを試す準備ができました。
APEX_DATA_LOADINGの呼び出し
ファイルをアップロードすると同時にデータのローディングを実行するように、プロセスを追加します。最初に作成済みのデータ・ロード定義CITYLISTを確認します。共有コンポーネントからデータ・ロード定義を開き、CITYLISTを開きます。
静的IDを確認します。今回作成したデータ・ロード定義CITYLISTの静的IDはCITYLISTでした。名前が英数字のみであれば静的IDとして名前が引き継がれますが、日本語を含む場合などはそうならないため、ユニークな値を入力する必要があります。
データ・ロードを定義する時点で主キーを設定していないため、ロード・メソッドとして追加が選ばれています。置換に変更することも可能です。今回は追加のままとします。
データ・ロードを行うファンクションはLOAD_DATAです。定義は以下になります。戻り値のタイプ定義は以下です。
type t_data_load_result is record(
processed_rows pls_integer,
error_rows pls_integer );processed_rowsはロードに成功した行数
error_rowsはロードに失敗した行数
です。
load_dataの定義は以下になります。
--==============================================================================
function load_data (
p_application_id in number default wwv_flow.g_flow_id,
p_static_id in varchar2,
p_data_to_load in blob,
p_xlsx_sheet_name in varchar2 default null )
return t_data_load_result;
--==============================================================================
function load_data (
p_application_id in number default wwv_flow.g_flow_id,
p_static_id in varchar2,
p_data_to_load in clob,
p_xlsx_sheet_name in varchar2 default null )
return t_data_load_result;
p_application_idはアプリケーションIDで、デフォルトは実行中のアプリケーションID(:APP_IDで取得できるID)
p_static_idはデータ・ロード定義の静的ID
p_data_to_loadはロードするデータを含むBLOBまたはCLOB
p_xlsx_sheet_nameはExcelのシート名、デフォルトはnullで先頭のシート
ファイルのアップロードを行った直後に、データを表FDL_CITYLISTヘロードする処理を追加します。
ロード処理を行うPL/SQLコードは以下になります。
declare
l_file blob;
l_res apex_data_loading.t_data_load_result;
begin
select content into l_file
from fdl_files where id = :P5_ID;
l_res := apex_data_loading.load_data
(
p_static_id => 'CITYLIST',
p_data_to_load => l_file
);
apex_debug.info('Processed Rows:' || l_res.processed_rows);
apex_debug.info('Error Rows:' || l_res.error_rows);
end;
このコードを、ファイルのアップロードと保存を行うフォーム(ページ番号5)にプロセスとして追加します。ファイルのアップロードは、タイプがフォーム - 行の自動処理(DML)のプロセスプロセス・フォームFdl Filesによって実行されるので、その直後にプロセスを作成します。
名前はデータのロードとしました。タイプはコードを実行、ソースのPL/SQLコードに上記のコードを記載します。サーバー側の条件として、ボタン押下時にCREATEを選択します。以上でレポート上の作成ボタンをクリックしてフォームを開いた後、ファイルを指定して作成をクリックすることによりファイルのアップロードが行われた直後に、表FDL_CITYLISTへデータがロードされます。
アプリケーションを実行し、動作を確認します。Citylistのレポートが空であることを確認し、Filesのレポートよりファイルcitylist.xlsxをアップロードします。結果としてCitylistのレポートにデータがロードされていることが確認できます。
ロード・メソッドは追加であるため、ファイルのアップロードを繰り返すと同じ内容のデータが追加されます。
ファンクションLOAD_DATAの呼び出しは非常に簡単ですが、本来であればソース・データ型をSQL Queryとし、以下のSQL問合せを実行することにより、データのロードができます。
select content
from fdl_files where id = :P5_ID
現時点ではソース・データ型にSQL Queryを指定すると、実行時にアイテムがみつかりません。というエラーが発生します。この現象は、すでに不具合として登録されています。この不具合が修正されるまでは、APEX_APPLICATION_TEMP_FILESではなく、自身で作成した表のBLOB列またはCLOB列からデータを取り込む際には、PL/SQLコードで対応する必要があります。
バックグラウンドでのデータ・ロードの実行
PL/SQLのコードを以下に変更することにより、データのロードをフォアグラウンドではなく、バックグラウンドで実行します。Oracle DatabaseのDBMS_SCHEDULERの機能を使用しています。
declare
l_job varchar2(4000);
begin
l_job :=
q'~
declare
l_file blob;
l_res apex_data_loading.t_data_load_result;
begin
apex_session.create_session(:APP_ID, :APP_PAGE_ID,':APP_USER');
select content into l_file
from fdl_files where id = :P5_ID;
l_res := apex_data_loading.load_data
(
p_static_id => 'CITYLIST',
p_data_to_load => l_file
);
apex_debug.info('Processed Rows:' || l_res.processed_rows);
apex_debug.info('Error Rows:' || l_res.error_rows);
end;
~';
l_job := replace(l_job, ':APP_ID', to_char(:APP_ID));
l_job := replace(l_job, ':APP_PAGE_ID', to_char(:APP_PAGE_ID));
l_job := replace(l_job, ':APP_USER', to_char(:APP_USER));
l_job := replace(l_job, ':P5_ID', to_char(:P5_ID));
dbms_scheduler.create_job(
job_name => 'LOAD_CITYLIST_' || sys_guid(),
job_type => 'PLSQL_BLOCK',
job_action => l_job,
start_date => sysdate,
enabled => TRUE,
auto_drop => TRUE
);
end;
元々のPL/SQLコードを、ほぼそのままDBMS_SCHEDULERのジョブとして実行しています。ジョブとして実行するにあたり、apex_session.create_sessionを実行し新規にセッションを開始しています。
apex_session.create_session(:APP_ID, :APP_PAGE_ID,':APP_USER');
今回のサンプルのcitylist.xlsxは８行しかデータが含まれていないため、変更前後での違いは体感できないでしょう。
ジョブの状態はビューUSER_SCHEDULER_JOB_RUN_DETAILSやUSER_SCHEDULER_RUNNING_JOBSから確認することができます。
このコードでは単純にユーザーのリクエストをバックグラウンドで実行しています。ユーザーが要求できるデータ・ロードの実行の回数や同時実行数に制限を加えたい場合などは、もっと作り込みが必要になると思います。例えばAskTOMでConner McDonaldさんが同様の質問に回答しています。
ページ作成ウィザードが作成するデータのロードのページは必ずAPEX_APPLICATION_TEMP_FILESを使用します。作成されたページを、ユーザーの表、今回の例では表FDL_FILESを使用するために必要な変更を紹介しようと思いましたが、記事が長くなったので、続く記事として記載します。
