e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第66回 「バルク・バインド FORALL のエラー処理」(2013.07.22)

2013.07.22

こんにちは。インストラクターの蓑島です。
今回は「バルク・バインドの最後回」として、仕上げのエラー処理について解説します。

配列データを使ったDMLをFORALLで一括処理を行うときに、データの内容が制約に違反するなどしてエラーになることがあります。そういった場合、FORALL処理は失敗して取消になります。
ところが、配列の中のどのデータで、どのようなエラーになったのかが、エラーメッセージだけでは判別できません。しかし、エラーを調べる手段が実は提供されているので、今回はその方法を簡単にご紹介します。

前回と同じ例で、エラーとなるFORALLを実行する準備をします。
そのために、TEST01表に、なにかの制約を設定しましょう。
一旦、TEST01表を削除します。

SQL> DROP TABLE TEST01;

表が削除されました。

改めて、制約つきのTEST01表を作成します。
SQL> 

1
2
3
4
5
6
CREATE TABLE TEST01
(  A NUMBER CONSTRAINT TEST01_PK  PRIMARY KEY ,
    B   VARCHAR2(10) CONSTRAINT TEST01_CHK CHECK ( B IN ( 'AA' , 'BB' , 'CC' )))
/
 
表が作成されました。

これでTEST01表が作成されました。
A列は、主キーです。
B列は、'AA','BB',CC'のいずれかの値が許可される制約がついています。

では、いつものように、パッケージに配列を用意します。

SQL>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE OR REPLACE PACKAGE PAC1
   /*******************************/
   -- パッケージを作る
   /*******************************/
IS
   /******************************/
   -- A列用の配列
   /******************************/
    TYPE A_TAB_TYPE IS TABLE OF TEST01.A%TYPE
    INDEX BY BINARY_INTEGER;
    A_TAB   A_TAB_TYPE;
   /******************************/
   --B列用の配列
   /******************************/
    TYPE B_TAB_TYPE IS TABLE OF TEST01.B%TYPE
    INDEX BY BINARY_INTEGER;
    B_TAB   B_TAB_TYPE;
END ;
/

パッケージが作成されました。
このパッケージのA_TAB配列、B_TAB配列に、A列、B列の制約に違反するデータを含めて値を格納します。

SQL> 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
BEGIN
   -- 添え字 1  OKのデータ
   PAC1.A_TAB(1) := 10 ;   PAC1.B_TAB(1) := 'AA' ;
   -- 添え字 2  OKのデータ
   PAC1.A_TAB(2) := 20 ;   PAC1.B_TAB(2) := 'BB' ;
   -- 添え字3  NGのデータ
   PAC1.A_TAB(3) := NULL ;   PAC1.B_TAB(3) := 'BB' ;
   -- 添え字4  OKのデータ
   PAC1.A_TAB(4) := 30 ;   PAC1.B_TAB(4) := 'CC' ;
   -- 添え字5  NGのデータ
   PAC1.A_TAB(5) := 10 ;   PAC1.B_TAB(5) := 'CC' ;
   -- 添え字6  OKのデータ
   PAC1.A_TAB(6) := 40 ;   PAC1.B_TAB(6) := 'CC' ;
   -- 添え字7  NGのデータ
   PAC1.A_TAB(7) := 50 ;   PAC1.B_TAB(7) := 'EE' ;
END ;
/

PL/SQLプロシージャが正常に完了しました。

これで、PAC1パッケージのA_TAB配列、B_TAB配列に添え字1~7までのデータが格納されましたが、コメントにあるように、添え字3,5,7のデータは、TEST01表にINSERTするときにエラーとなります。
理由は以下の通りです。

添え字3については、A列の値がNULLです。A列は主キーなので、NULLはエラーとなります。
添え字5については、A列の値が10です。
すでに添え字1でA列の値が10のINSERTが行われるので、主キーの値が重複することになりエラーです。
添え字7については、B列の値が'EE'です。
B列には制約があり、許可される値は、'AA','BB','CC'のみです。
したがって'EE'は許可されない値なので、TEST01表にINSERTするとエラーになります。

ではこの配列データを使って、早速、TEST01表にFORALLでINSERTしてみましょう。
当然、エラーとなります。

SQL>

1
2
3
4
5
BEGIN
   FORALL I IN  PAC1.A_TAB. FIRST ..PAC1.A_TAB. LAST
     INSERT INTO TEST01(A,B) VALUES (PAC1.A_TAB(I),PAC1.B_TAB(I));
END ;
/

BEGIN
*
行1でエラーが発生しました。:
ORA-01400: ("SCOTT"."TEST01"."A")にはNULLは挿入できません。
ORA-06512: 行2

予定どおり、エラーとなりました。

ここでは、添え字3のところでA列の値がNULLなので、上記のエラーとなったわけです。
添え字3のエラーで、FORALL文そのものが取り消しになりますので、添え字1と2のINSERTは取消になります。
添え字4以降の処理はまったく行われていません。
したがってこの処理でINSERTされた行はありません。

実際にTEST01表を問い合わせてみます。

SQL> SELECT * FROM TEST01;

レコードが選択されませんでした。

ご覧のように、0件ですね。

しかし、配列データの中にエラーとなるデータが含まれていても、エラーにならないデータで処理を行いたいニーズもあります。
そういった場合は、FORALL文のDML文の前に、「SAVE EXCEPTIONS」のキーワードを付けます。
しかし後述しますが、これだけでは不十分です。

実際にやってみます。

1
2
3
4
5
6
BEGIN
   FORALL I IN  PAC1.A_TAB. FIRST ..PAC1.A_TAB. LAST
     SAVE  EXCEPTIONS        -- ★★
     INSERT INTO TEST01(A,B) VALUES (PAC1.A_TAB(I),PAC1.B_TAB(I));
END ;
/

ERROR:
ORA-24381: DML配列にエラーがあります。
ORA-06512: 行2

やはり、これでもエラーになりました。

上記のPL/SQLブロックのコードでは、3行目で、「SAVE EXCEPTIONS」キーワードをつけたことで、配列データにエラーがあっても、配列データの最後まで処理を行いました。
そして、結果的に配列データにエラーがあったことを「ORA-24381: DML配列にエラーがあります。」のエラーメッセージで教えてくれたわけです。
もしこのエラーメッセージが表示されなければ、配列データにエラーがあったことがわかりません。
ここで問題なのは、このエラーによって、PL/SQLブロックはエラーのステータスで終了していることです。
ブロックのステータスがエラーなので、結局このブロック内の処理は取り消され、TEST01表には1行もINSERTされていません。

ですから、「SAVE EXCEPTONS」キーワードを使うのであれば、必ず「ORA-24381」エラーの例外ハンドラを用意して、エラーが起きても、正常終了するようにしなければいけません。
ではその場合の例外処理の内容をどのようにすべきでしょうか。
この場合は、エラーの起きた配列データの情報を収集する処理が一般的です。
やはり、どの配列データでエラーとなったのかを知る必要があります。

そこで、その処理を組み込んだ最終的な例として、以下のコードをご覧ください。
ここでは単純にエラーとなる配列の添え字番号と、そのエラーメッセージを表示しましょう。

SQL> 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
DECLARE
    ERR_COUNT  number;
BEGIN
   FORALL I IN  PAC1.A_TAB. FIRST ..PAC1.A_TAB. LAST
     SAVE  EXCEPTIONS
     INSERT INTO TEST01(A,B) VALUES (PAC1.A_TAB(I),PAC1.B_TAB(I));
EXCEPTION
   WHEN others  THEN
    IF SQLCODE = -24381  THEN    -- ORA-24318エラーの時、以下のエラー処理
      ERR_COUNT  := SQL%BULK_EXCEPTIONS. COUNT -- エラーデータの件数取得
      DBMS_OUTPUT.PUT_LINE
       ( 'エラーデータの数: ' || ERR_COUNT);
      FOR i IN 1..ERR_COUNT LOOP
         -- エラーの添え字番号表示
         DBMS_OUTPUT.PUT( '添え字番号:' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX || ' ' );
         -- エラーメッセージ ( エラーコード⇒ sqlerrmファンクション)
         DBMS_OUTPUT.PUT_LINE(sqlerrm(-1 * SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
      END LOOP;
    END IF;
END ;
/

エラーデータの数: 3
添え字番号:3 ORA-01400: ()にはNULLは挿入できません。
添え字番号:7 ORA-02290: チェック制約(.)に違反しました
添え字番号:5 ORA-00001: 一意制約(.)に反しています

PL/SQLプロシージャが正常に完了しました。

簡単に解説します。

まず、10行目の「SQL%BULK_EXCEPTIONS.COUNT」に注目してください。
これによりエラーとなった配列データの数を取得しています。
そしてその数だけ、以下の処理でループしてエラーデータの詳細を画面表示しています。

15行目の「SQL%BULK_EXCEPTIONS(i).ERROR_INDEX」は、エラーとなった配列の添え字番号を返します。

17行目の「SQL%BULK_EXCEPTIONS(i).ERROR_CODE」はエラー番号なのですが、この番号に「-1」を掛けたものをsqlerrm関数に渡してエラーメッセージを取得し表示しています。
通常、エラー番号はマイナスなのですが、SQL%BULK_EXCEPTIONS(i).ERROR_CODEは、プラスの値でエラーコードを返しますので、「-1」を掛ける必要があります。

以上の対応で、PL/SQLブロックは正常終了していますので、TEST01表にはエラーではないデータは格納されたはずです。
問い合わせてみましょう。

SQL> SELECT * FROM TEST01;

         A        B
---------- ----------
        10        AA
        20        BB
        30        CC
        40        CC

エラーではないデータはきちんと格納されていますね。

これで、配列データの一部にエラーがあっても、エラーでないデータでFORALLできますし、どの添え字で、どんなエラーだったかということも調べられるわけです。
これで大量データでも安心してFORALLできますね。

それでは、今回はここまでにします。
約2か月にわたってバルク・バインドを解説してきました。

配列を使ったDML処理には、FORALL、複数行を一括で取得するには、BULK COLLECT INTO、ということで、ぜひ試してみてください。

今回でバルク・バインドは一旦終え、次回からまた違ったテーマを用意します。
どうぞご期待ください。

先頭へ戻る