Oracle ストアドプロシージャでのBULK INSERT

Question

前提・実現したいこと

oralceにて、他システムで保持しているユーザー情報の移行が必要となり、
件数が膨大（数百万件）である為ストアドを作成し移行作業を行おうとしています。
概要としましては移行用のcsvファイルをloaderで移行会員TBL（MIG_CUSTOMER）に読み込み、
それを加工し複数のTBLにデータを挿入するという流れです。（CUSTOMER,ADDRESS）
移行用TBLへの読み込みは完了しています。

カーソルを作成しFOR LOOPで回し、加工して１件ずつ挿入という流れを取ったところ、
処理時間が200万件のデータで10時間を超えてしまい、チューニングに困っています。
そこでBULK INSERTなる物を使用せんと調べて実装しているのですが、
TBL間の直接のデータの移行には成功しましたがデータの加工方法がわからず苦戦しています。

下記ソースコードはコンパイルエラーとなり、FORALL文の後にはINSERT等を続けなければいけない旨はわかりましたが、
データの加工方法をご教示いただけますと幸いです。
（そもそもやろうとしていることが本当に可能なのかどうかから不明です）

よろしくお願い致します

発生している問題・エラーメッセージ

Error(50,3): PLS-00103: 記号"CUSTOMER_REC"が見つかりました。 次のうちの1つが入るとき:     . ( * @ % & - + / at mod remainder rem select update with    <an exponent (**)> delete insert || execute multiset save    merge 記号"." は続行のために"CUSTOMER_REC"に代わりました。 

該当のソースコード

-- CREATE文の一部を記載します
CREATE TABLE M_MIG_CUSTOMER_BTR
(
    CUSTOMER_CODE                   NUMBER(12,0)

)
/
CREATE INDEX M_CUSTOMER_IDX2
    ON M_MIG_CUSTOMER_BTR (CUSTOMER_CODE)

---

CREATE TABLE CUSTOMER
(
    CUSTOMER_ID                     VARCHAR2(12) NOT NULL,
    OLD_CUSTOMER_ID                 VARCHAR2(13)
)
/
ALTER TABLE CUSTOMER
    ADD(CONSTRAINT PK_CUSTOMER PRIMARY KEY (CUSTOMER_ID) USING INDEX)

---

CREATE OR REPLACE PROCEDURE BULK_INSERT
IS
  BULK_SIZE PLS_INTEGER := 1000;

  CURSOR CSR_CUSTOMER IS
    SELECT * FROM MIG_CUSTOMER
    ORDER BY CUSTOMER_CODE ASC ;
    
  TYPE CSR_CUSTOMER_REC IS TABLE OF CSR_CUSTOMER%ROWTYPE;
  TYPE CUSTOMER_REC IS TABLE OF CUSTOMER%ROWTYPE;

  V_CSR_CUSTOMER_REC CSR_CUSTOMER_REC;
  V_CUSTOMER_REC CUSTOMER_REC;
 
  -- エラーハンドラ用
  vBulkErrors  PLS_INTEGER := 0;
  eBulkProcessNotComplete EXCEPTION;
  PRAGMA EXCEPTION_INIT(eBulkProcessNotComplete, -24381);
BEGIN
 
  OPEN CSR_CUSTOMER;
  LOOP
    FETCH CSR_CUSTOMER BULK COLLECT INTO V_CSR_CUSTOMER_REC LIMIT BULK_SIZE;
    -- BULK FETCH 処理
    EXIT WHEN V_CSR_CUSTOMER_REC.COUNT = 0;
    DBMS_OUTPUT.PUT_LINE('バルクインサート対象件数:' || V_CSR_CUSTOMER_REC.COUNT);
    BEGIN
      -- BULK INSERT 処理
      FORALL i in 1..V_CSR_CUSTOMER_REC.COUNT SAVE EXCEPTIONS
      -- 会員情報セット（ここの処理の記述方法が不明）
      CUSTOMER_REC(i).CUSTOMER_ID                     := '9999999999';
      CUSTOMER_REC(i).OLD_CUSTOMER_ID                 := LPAD(V_CSR_CUSTOMER_REC(i).CUSTOMER_CODE,10,'0');

      INSERT INTO CUSTOMER VALUES V_CSR_CUSTOMER_REC(i);

      COMMIT;
    EXCEPTION
      WHEN eBulkProcessNotComplete THEN
        vBulkErrors := vBulkErrors + SQL%BULK_EXCEPTIONS.COUNT;
        -- PROC_BULK_ERROR_HANDLER;
    END;
  END LOOP;
  CLOSE CSR_CUSTOMER;
END;

試したこと

加工せずそのままの値を移植するように記述した際は正常に動作確認ができました。

補足情報（FW/ツールのバージョンなど）

実際に行いたい加工の項目は100を超えるため割愛しています。

CUSTOMER.CUSTOMER_IDは本来主キーですが、動作確認の為固定値を入れています。
（余分な処理は記載せず、一意制約エラーまで行くのを確認したい為）
ADDRESSへの挿入は割愛していますが、同様にMIG_CUSTOMERから取得した物を加工して挿入したいです。
複数TBLへのINSERT処理を同時に行いたいと思っています。
（CUSOTOMER_RECに情報を詰めた後はADDRESS_RECに情報を詰め、連続してINSERTするイメージ）

Answer 1

VALUES の内容はinsertする形に合わせましょう。
即ち、カーソル内で会員情報セットなどの整形は済ませておくことです。

追記

一つのテーブルから複数のテーブルに纏めて追加したいとのことですので、INSERT ALLの方が良いかと思います。
フェッチしながら、複数件数をある程度纏めてからSQL発行してコミット。

Comments

[shirobara_]

回答ありがとうございます。
ご指摘の内容ですと、下記のようなイメージでしょうか？

　CURSOR CSR_CUSTOMER IS
SELECT '9999999999' AS CUSTOMER_ID,
LPAD(V_CSR_CUSTOMER_REC(i).CUSTOMER_CODE,10,'0') AS OLD_CUSTOMER_ID
FROM MIG_CUSTOMER
ORDER BY CUSTOMER_CODE ASC ;
(中略）
FORALL i in 1..V_CSR_CUSTOMER_REC.COUNT SAVE EXCEPTIONS
INSERT INTO CUSTOMER VALUES V_CSR_CUSTOMER_REC(i)

このようにした際、カーソルで取得した内容をCUSTOMERやADDRESSに分けて挿入したい場合、
下記のようになるかなと考えます。

INSERT INTO CUSTOMER VALUES
(V_CSR_CUSTOMER_REC(i).CUSTOMER_ID,
V_CSR_CUSTOMER_REC(i).OLD_CUSTOMER_ID);

この場合、仕様するカラム数が増えるとカーソル内の記述とINSERT文の記述でかなり冗長になってしまうかなと感じたのですが、そういうものと解釈するほかないのでしょうか。

頂いた回答に対する私の解釈が誤っていましたら申し訳ありません。

[sazi]

なぜ、カーソルの構造とINSERT時のVALUESの構造が別だと思うのですか？
> FETCH CSR_CUSTOMER BULK COLLECT INTO V_CSR_CUSTOMER_REC LIMIT BULK_SIZE;
> INSERT INTO CUSTOMER VALUES V_CSR_CUSTOMER_REC(i);
そもそも、上記が成り立つためにはそれぞれの構造が
CUSTOMER=V_CSR_CUSTOMER_REC=CSR_CUSTOMER
でないと駄目なのですよ？

[sazi]

SQLloaderでの時間は許容でき、CSVデータに更新日時等のタイムスタンプ情報などで変更が識別できるなら、事前に全量移行しておき、切り替え当日は差分移行するのもありだと思います。

[shirobara_]

＞そもそも、上記が成り立つためにはそれぞれの構造が
＞CUSTOMER=V_CSR_CUSTOMER_REC=CSR_CUSTOMER
＞でないと駄目なのですよ？
ということはつまりカーソルでとってきたデータを加工して複数テーブルに挿入するのは無理ということなのでしょうか。
SQLの仕組みについて理解が浅くいまいちわかっていませんが、
なんとなく自分が見当違いの事をしているようである旨は理解致しました。
もう少し調べたうえで考えてみようと思います。ありがとうございます。

差分移行については最悪そうする気ではおります、ありがとうございます。

[sazi]

> カーソルでとってきたデータを加工して複数テーブルに挿入するのは無理ということ
そんな事は一言も云っていません。
質問のSQLの内容について述べているだけです。

INSERT ALLに関して追記しました。

[sazi]

元々の処理が、全件COMMITで行っているようなら、一定件数毎にCOMMITするようにしてみるとか。

[shirobara_]

話し合った結果、差分移行とする方向になりました。
ご指摘いただいた内容は今後の学習に役立てようと思います。
私のような初学者へのアドバイス、ありがとうございました。

Answer 2

質問にCREATE TABLE文も載せてください。

とりあえず、

-- 宣言
  TYPE CSR_CUSTOMER_REC IS TABLE OF CSR_CUSTOMER%ROWTYPE;

  V_CSR_CUSTOMER_REC CSR_CUSTOMER_REC;

      CUSTOMER_REC(i).CUSTOMER_ID                     := '9999999999';
      CUSTOMER_REC(i).OLD_CUSTOMER_ID                 := LPAD(V_CSR_CUSTOMER_REC(i).CUSTOMER_CODE,10

と、CUSTOMER_REC が定義されてません。

Comments

[shirobara_]

回答ありがとうございます。
実際のテーブルはカラム数が多い為割愛したのですが、今回のエラーの判断に必要でしたでしょうか？
（コンパイル不可の為、PL/SQLの文法の問題でありテーブル定義はまた別の話かと認識しておりました。）

また、CUSTOMER_REC についてですが
CUSTOMER_REC CUSTOMER%ROWTYPE;
の箇所で定義しているつもりなのですが、誤っていますでしょうか。
お手数おかけしまして申し訳ありませんが、よろしくお願い致します。

[Orlofsky]

> CUSTOMER_REC CUSTOMER%ROWTYPE;
は配列の定義ではないので、USTOMER_REC(i) とは記述できません。

[Orlofsky]

Oracle Database がEnterprise Edition なら、パラレル処理で insert ... select ... でやった方がてっとり早いかも？
https://www.oracle.com/technetwork/jp/ondemand/database/db-technique/parallel-exe-evening-100714-251722-ja.pdf

[shirobara_]

回答ありがとうございます。
そうだったのですね！
学習不足でした、ありがとうございます。

TYPE CSR_CUSTOMER_REC IS TABLE OF CSR_CUSTOMER%ROWTYPE;
TYPE CUSTOMER_REC IS TABLE OF CUSTOMER%ROWTYPE;

V_CSR_CUSTOMER_REC CSR_CUSTOMER_REC;
V_CUSTOMER_REC CUSTOMER_REC;

とし、会員情報そセットする際にV_CUSTOMER_REC(i)としましたが同様のエラーとなりました。

[shirobara_]

パラレル処理、知らない内容でした、ありがとうございます。
しかし確認したところ、使用しているのはOracle Database 11g Express Edition
となっており、使用できなさそうです・・・

[Orlofsky]

パラレル処理を先にやってみては？
質問のコードを修正してください。
あと、変数の用途が分かりにく過ぎです。もう少しわかり易いネーミングを考えては？変数の定義の右に -- 用途
を記述するとか。

[shirobara_]

コード修正致しました。
変数についてはすみません、
コピペしてきた箇所といろいろ試しているうちに変更した箇所とでわかりにくくなってしまっていますね。
変更を考えてみます。

[Orlofsky]

>Oracle Database 11g Express Edition
最初に質問に書くべきです。パラレル処理は DBMS_PARALLEL_EXECUTE
https://docs.oracle.com/cd/E16338_01/appdev.112/b56262/d_parallel_ex.htm#CHDIJACH
が必要なのでとらえず保留。
パラレル処理なしで insert ... select ... でやってみては？
200万件あるデータでExpress Edition って選択がそもそも間違いです。

[shirobara_]

oracleにバージョン以外の差異があることも知らぬ素人でして、申し訳ありません。
BULKではなくinsert selectでの処理を試してみようかと思います。
Editionは業務システムであるため如何ともしがたく・・・
ともあれ、アドバイス頂きありがとうございます。