日日是Oracle APEX: CSVでアップロードした行の差分を確認する

2021年3月23日火曜日

CSVでアップロードした行の差分を確認する

データ・ロード・ウィザードでCSVファイルを繰り返しアップロードしたときに、直近のアップロードに含まれていない行を確認したい、との相談がありました。データ・ロード・ウィザードの場合、主キー項目がなければ新規行として挿入、あればその行が更新されますが、アップロードしたデータに含まれない行はそのまま残ります。

そのまま残っている行を対話モード・レポートでハイライトさせます。

アップロードする前にデータを全削除（またはバックアップを取って全削除）する、トリガーを追加し、新規行と更新行にフラグを立てる、といった方法も考えられますが、今回はオラクル・データベースの機能であるORA_ROWSCN疑似列を使ってみることにしました。

アップロードするCSVファイルは以下から取得します。

https://apex.oracle.com/pls/apex/japancommunity/r/simcontents/download?id=Project_and_Tasks_ja.csv

上記のCSVファイルを取り込む表をクリックSQLのモデルとして定義します。

# prefix: hld
# semantics: default
tasks
    project vc255
    task_name vc255
    start_date
    end_date
    status vc8
    assigned_to vc80
    cost num
    budget num

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

表HLD_TASKSを作成するCREATE文の末尾にROWDEPENDENCIESを追加し、ORA_ROWSCN疑似列が、それぞれの行の変更時のSCNを持つようにします。デフォルトはNOROWDEPENDENCIESで、ORA_ROWSCNは行ではなくブロック単位での変更時のSCNを保持します。

CREATE文を実行し表HLD_TASKSを作成したのち、アプリケーションの作成を実行し、アプリケーション作成ウィザードを起動するところまで進みます。名前を削除行確認とし、アプリケーションの作成を行います。対話モード・レポートのページは、アプリケーション作成ウィザードが作成します。

アプリケーションが作成されたら、データ・ロード・ウィザードのページを作成します。ページの作成を開始します。

コンポーネントのデータのロードを選びます。

定義名は任意ですが、今回はタスクとしました。表名はHLD_TASKS、一意列（の列1）にはID (Number)を選択し、次へ進みます。

トランスフォーメーション・ルール、表ルックアップは何も定義せず、そのまま次へ進みます。ページ属性もデフォルトをそのまま使用し、次へ進みます。

サイド・メニューよりデータ・ロード・ウィザードを開始できるよう、ナビゲーションのプリファレンスとして新規ナビゲーション・メニュー・エントリの作成を選択します。次に進みます。

「取消」ボタンでブランチするページ、および、ページへの「終了」ボタン・ブランチを２、つまり対話モード・レポートのページに設定します。作成をクリックします。

データ・ロード・ウィザードのページが追加されたので、アプリケーションを実行してCSVファイルをロードします。

アプリケーションにサインインし、サイド・メニューよりデータのロードを実行します。インポート先としてファイルをアップロード(カンマ区切り(*.csv)またはタブ区切り)を選びます。１行名に列名があるをチェックします。詳細設定の使用にチェックを入れて、ファイルの文字セットとして日本語(Shift_JIS)を選択します。設定を終えたら次に進みます。

データと表のマッピングを確認して次に進みます。（表の列名とCSVの先頭行の列名は一致するようにしているため、自動的にマッピングされます）

データ検証のページが開きます。アクションはすべて行の挿入で、全部で７３件になっています。次に進みます。

データ・ロード結果として、挿入された行が73であることを確認し、終了します。

対話モード・レポートのページが表示されます。データがロードされていることが確認できます。

挿入および更新された行と削除された行を示す列ROW_STATUSを追加します。追加する列の定義は以下になります。一番最近に更新された行についてはUを返し、それ以外はDを返します。対話モード・レポートや他の操作で行が更新されると、その行が最新のSCN番号を持つため、列ROW_STATUSに意味が無くなります。列ROW_STATUSはデータ・ロード・ウィザードの実行直後で、その後にデータが更新される前まで有効です。

       case
       when ora_rowscn = (select max(ora_rowscn) from hld_tasks) then
       'U'
       else
       'D'
       end row_status

対話モード・レポートのソースのタイプをSQL問合せに変更し、以下のSQLを設定します。

select ID,
       PROJECT,
       TASK_NAME,
       START_DATE,
       END_DATE,
       STATUS,
       ASSIGNED_TO,
       COST,
       BUDGET,
       case
       when ora_rowscn = (select max(ora_rowscn) from hld_tasks) then
       'U'
       else
       'D'
       end row_status
from HLD_TASKS