BULK COLLECT - SAVE EXCEPTIONS

CREATE TABLE MyTable
(
       num_col    NUMBER,
       char_col   VARCHAR2(60)
);

 DECLARE
  TYPE t_strings IS TABLE OF mytable.char_col%TYPE INDEX BY BINARY_INTEGER;
  TYPE t_numbers IS TABLE OF mytable.num_col%TYPE INDEX BY BINARY_INTEGER;
  v_strings   t_strings;
  v_numbers   t_numbers;
  v_numerrors NUMBER;
BEGIN
  DELETE FROM mytable;
  FOR v_count IN 1 .. 10 LOOP
    v_strings(v_count) := '123456789012345678901234567890';
    v_numbers(v_count) := v_count;
  END LOOP;

  FORALL v_count IN 1 .. 10
    INSERT INTO mytable
      (num_col, char_col)
    VALUES
      (v_numbers(v_count), v_strings(v_count));

  v_strings(6) := v_strings(6) || 'a';

  FORALL v_count IN 1 .. 10 SAVE EXCEPTIONS
    UPDATE mytable
       SET char_col = char_col || v_strings(v_count)
     WHERE num_col = v_numbers(v_count);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Got exception: ' || SQLERRM);
    v_numerrors := SQL%bulk_exceptions.count;
    dbms_output.put_line('Number of errors during processing: ' ||
                         v_numerrors);
    FOR v_count IN 1 .. v_numerrors LOOP
      dbms_output.put_line('Error ' || v_count || ', iteration ' || SQL%BULK_EXCEPTIONS(v_count)
                           .error_index || ' is: ' ||
                           SQLERRM(0 - SQL%BULK_EXCEPTIONS(v_count)
                                   .error_code));
    END LOOP;

    COMMIT;
END;

/

----Output---------------

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: )


I hope you all have enjoyed reading this article. Comments are welcome....

Related Posts:

- Collection Comparison
- Working with Collections
- Bulk Processing with BULK COLLECT and FORALL