以下のようなExcelファイルのアップロードを行う実装を行ってみます。Excelファイルのファイル名はcitylist.xlsxとします。
その１として、
表APEX_APPLICATION_TEMP_FILESを使う方法
その２として、
作成済みの表のBLOB列を使う方法
の２つの方法に取り組みます。
最初であるこの記事では、表APEX_APPLICATION_TEMP_FILESを使った方法を実装します。
ファイルのアップロードを行うアプリケーションは、いままでに何件か記事を書いています。今回は、Oracle APEX 21.1で実装される予定のデータ・ロードの機能を手作業で作ることで、その実装を理解することを目的としています。
アップロードする表は、以下のクイックSQLのモデルによって作成します。
名前をAPEX標準表、ページ・モードは標準、オプションの静的コンテンツ・リージョンとして、リージョンをひとつ、リージョン1としてファイルのアップロードを作成します。次に進みます。
終了をクリックすると、静的コンテンツのリージョンがひとつあるページが作成されます。
実行オプションのポイントに送信後(送信後に計算を行うという意味)を選択します。計算のタイプとしてSQL問合せ(単一の値を返す)を選択し、以下のSQL問合せを設定します。
プレビューに使用したSELECT文ですが、この後に、このSELECT文の結果を表FUP_CITYLISTに挿入する処理を追加します。
続いて、ボタンB_SUBMITが押された時に実行されるプロセスを作成します。名前を表にロードとし、タイプはコードを実行、ソースの位置はローカル・データベース、言語はPL/SQLを選択し、PL/SQLコードとして以下を記述します(プレビューではないのでp_max_rowsの指定を除いています)。サーバー側の条件として、ボタン押下時にB_SUBMITを指定することにより、送信ボタンを押した時のみ実行されるようにします。
# prefix: fup
# semantics: default
citylist
prefecture vc80
city vc80
count num
実際に実行するDDLとしては以下になります。
create table fup_citylist (
id number generated by default on null as identity
constraint fup_citylist_id_pk primary key,
prefecture varchar2(80),
city varchar2(80),
count number
)
;
ファイルのアップロードを実装するために、空のアプリケーションを作成します。アプリケーションの名前はファイルのアップロードとします。アプリケーションの作成を実行します。
空のアプリケーションが作成されます。
最初にOracle APEXが提供している表APEX_APPLICATION_TEMP_FILESに一旦ファイルをアップロードしたのち、対象の表FUP_CITYLISTヘデータの投入を行うページを作成します。
静的コンテンツのリージョンを含むページを作成します。ページの作成を実行し、ページ作成ウィザードを開始します。
空白ページをクリックします。
ナビゲーションのプリファレンスとして、新規ナビゲーション・メニュー・エントリの作成を選択します。次に進みます。
ページが作成されたら、アップロードするファイルを選択するページ・アイテムを作成します。識別の名前をP2_FILE、タイプはファイル参照...を指定します。ラベルとしてアップロードするファイル、表示形式として今回はBlock Dropzoneにしてみます。表示形式だけなので、どれを選んでもファイルは選択できます。ドロップ・ゾーンの説明としてExcelファイル(XLSX形式)をドロップしてください。と記述します。記憶域タイプとしてTable APEX_APPLICATION_TEMP_FILESを選択します。ファイルをパージするタイミングはEnd of Sessionとします。最大ファイル・サイズとして5000KBの制限を与えています。
一旦ファイルが選択されたら、その後にページ・アイテムが変更されることを防ぐため、サーバー側の条件でタイプにアイテムがNULLを選択し、アイテムにP2_FILEを指定します。この設定によりファイルが未選択のときに限り、ファイル参照が表示されます。
ファイルが選択された時点で表APEX_APPLICATION_TEMP_FILESに保存されるよう、動的アクションを作成します。ページ・アイテムP2_FILE上でコンテキスト・メニューを表示させ、動的アクションの作成を実行します。
動的アクションの名前をファイルのアップロードとします。タイミングはデフォルトでイベントが変更、選択タイプはアイテム、アイテムはP2_FILEとなり、P2_FILEが変更されるとアクションが実行されます。
Trueアクションを選択し、アクションとしてページの送信を選択します。
この時点でファイルのアップロードまでは実装できています。
アップロードされた結果を確認するために、アップロードされたファイルのファイル名を表示するページ・アイテムを追加します。
ページ・アイテムを作成し、名前をP2_FILE_NAME、タイプは表示のみ、ラベルをファイル名とします。
ファイルのアップロード後、表APEX_APPLICATION_TEMP_FILESから登録されたファイル名を取り出し、ページ・アイテムP2_FILE_NAMEに設定します。ページ・アイテムP2_FILE_NAMEに計算の作成を行います。
select filename
from apex_application_temp_files
where name = :P2_FILE
以上でアップロードされたファイルのファイル名が、ページ・アイテムP2_FILE_NAMEに表示されます。さらに、アップロードされたデータをプレビューします。
クラシック・レポートのリージョンを追加します。
リージョンを作成し、名前をプレビュー、タイプをクラシック・レポートとします。ソースの位置はローカル・データベース、タイプをSQL問合せとし、以下のSELECT文を記載します。
select
col001 "PREFECTURE",
col002 "CITY",
col003 "COUNT"
from
apex_data_parser.parse(
p_content =>
(
select blob_content from apex_application_temp_files
where name = :P2_FILE
),
p_file_name => :P2_FILE_NAME,
p_skip_rows => 1,
p_file_charset => 'AL32UTF8',
p_max_rows => 10
)
ここまでの動作は以下のようになります。
表FUD_CITYLISTにデータを投入するプロセスを作成します。データの送信を行うボタンを作成します。ボタン名をB_SUBMIT、ラベルを送信とします。
begin
delete from fup_citylist;
insert into fup_citylist(prefecture, city, count)
select
col001 "PREFECTURE",
col002 "CITY",
col003 "COUNT"
from
apex_data_parser.parse(
p_content =>
(
select blob_content from apex_application_temp_files
where name = :P2_FILE
),
p_file_name => :P2_FILE_NAME,
p_skip_rows => 1,
p_file_charset => 'AL32UTF8'
);
end;
上記のコードは、送信をクリックした際に、すでに保存されているデータをすべて削除し、新たにアップロードしたデータで入れ替えます。追加やマージといった動作にしたい場合は、PL/SQLのコードを変更することになります。
以上で表へのデータのロードも実行されるようになりました。動作確認をもう少し簡単にするため、ページを初期化するボタンと表FUD_CITYLISTの内容を表示するレポートを追加します。
ボタンの作成を行い、ボタン名をB_CLEAR、ラベルをクリアとします。
ボタンB_CLEARを押した時に実行されるプロセスを作成します。作成したプロセスの名前をページの初期化とし、タイプにセッション・ステートのクリアを選択します。サーバー側の条件として、ボタン押下時にB_CLEARを選択します。これでクリアのボタンを押した時に、ページが初期化されます。
アップロード結果を表示するクラシック・レポートを作成します。
名前をアップロード結果とし、タイプはクラシック・レポートを選択します。ソースの表名にFUP_CITYLISTを選択し、プレビューと横並びになるよう、レイアウトの新規行の開始をOFFとします。
以上で完成です。調整すべきところはありますが(例えばプレビューにORA-20987のエラーが発生するなど)、当初の目的は達成しています。
ページを実行し、実装を確認します。以下の動作になります。
作成済みの表のBLOB列を使う方法は、次の記事として記載します。
