The SAVE EXCEPTIONS clause will record any exception during the bulk operation, and continue processing. : FORALL « PL SQL « Oracle PL / SQL






The SAVE EXCEPTIONS clause will record any exception during the bulk operation, and continue processing.

  
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );

Table created.

SQL>
SQL>
SQL> DECLARE
  2    TYPE t_Strings IS TABLE OF MyTable.char_col%TYPE
  3      INDEX BY BINARY_INTEGER;
  4    TYPE t_Numbers IS TABLE OF MyTable.num_col%TYPE
  5      INDEX BY BINARY_INTEGER;
  6    v_Strings t_Strings;
  7    v_Numbers t_Numbers;
  8    v_NumErrors NUMBER;
  9  BEGIN
 10    DELETE FROM MyTable;
 11    FOR v_Count IN 1..10 LOOP
 12      v_Strings(v_Count) := '123456789012345678901234567890';
 13      v_Numbers(v_Count) := v_Count;
 14    END LOOP;
 15
 16    FORALL v_Count IN 1..10
 17      INSERT INTO MyTable (num_col, char_col)
 18        VALUES (v_Numbers(v_Count), v_Strings(v_Count));
 19
 20    v_Strings(6) := v_Strings(6) || 'a';
 21
 22    FORALL v_Count IN 1..10 SAVE EXCEPTIONS
 23      UPDATE MyTable
 24        SET char_col = char_col || v_Strings(v_Count)
 25        WHERE num_col = v_Numbers(v_Count);
 26  EXCEPTION
 27    WHEN OTHERS THEN
 28      DBMS_OUTPUT.PUT_LINE('Got exception: ' || SQLERRM);
 29      v_NumErrors := SQL%BULK_EXCEPTIONS.COUNT;
 30      DBMS_OUTPUT.PUT_LINE(
 31        'Number of errors during processing: ' || v_NumErrors);
 32      FOR v_Count IN 1..v_NumErrors LOOP
 33        DBMS_OUTPUT.PUT_LINE('Error ' || v_Count || ', iteration ' ||
 34          SQL%BULK_EXCEPTIONS(v_Count).error_index || ' is: ' ||
 35          SQLERRM(0 - SQL%BULK_EXCEPTIONS(v_Count).error_code));
 36      END LOOP;
 37
 38      COMMIT;
 39  END;
 40  /
Got exception: ORA-24381: error(s) in array DML
Number of errors during processing: 1
Error 1, iteration 6 is: ORA-12899: value too large for column  (actual: , maximum: )

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table MyTable;

Table dropped.

SQL>

   
  








Related examples in the same category

1.forall from 1 to 50
2.Insert all 1000 elements using a single FORALL statement
3.An exception will stop the bulk insert.
4.Use a FORALL to move an associative array into a table