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