Handling Exceptions With Bulk Operations In Oracle

When working large amount of data, you must consider exceptions. Suppose that a bulk operation that takes 100.000 of records and inserts it to a table. There may have some exceptions during insertion such as foreign key or not null constraints. If you did not consider exceptions, you can lose your time and do more work. One other assumption that can be, you can get an error on the last record. So all of your correct data can get waste.
Oracle solves this problems with some techniques. One of them is save bulk_exceptions in forall statement. Other is dbms_errlog package. I have described dbms_errlog package one of my older posts. (For performance analysis click here)
Demonstration below simple shows the cases above:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS
SQL>
SQL> drop table t;
Table dropped

Create a table and add some constraints.
SQL> create table t(i number not null);
Table created
SQL> alter table t add constraint ck_i check( i < 2 );
Table altered

Make insertion with normal way. You will get an exception and all work will be rolled back.
SQL> DECLARE
  2    TYPE t_t IS TABLE OF NUMBER;
  3    n_t t_t;
  4  BEGIN
  5    SELECT decode(rownum, 2, NULL, rownum) BULK COLLECT
  6      INTO n_t
  7      FROM user_tables
  8     WHERE rownum < 4;
  9    FORALL i IN n_t.FIRST .. n_t.LAST
 10      INSERT INTO t VALUES( n_t (i) );
 11  END;
 12  /
DECLARE
  TYPE t_t IS TABLE OF NUMBER;
  n_t t_t;
BEGIN
  SELECT decode(rownum, 2, NULL, rownum) BULK COLLECT
    INTO n_t
    FROM user_tables
   WHERE rownum < 4;
  FORALL i IN n_t.FIRST .. n_t.LAST
    INSERT INTO t VALUES( n_t (i) );
END;
ORA-01400: cannot insert NULL into ("SYS"."T"."I")
ORA-06512: at line 9
SQL> SELECT * FROM t;
         I
----------
SQL> rollback;
Rollback complete

First technique is handling in forall statement. You will get correct records in table.
SQL> DECLARE
  2    TYPE t_t IS TABLE OF NUMBER;
  3    n_t t_t;
  4  BEGIN
  5    SELECT decode(rownum, 2, NULL, rownum) BULK COLLECT
  6      INTO n_t
  7      FROM user_tables
  8     WHERE rownum < 4;
  9    FORALL i IN n_t.FIRST .. n_t.LAST SAVE EXCEPTIONS
 10      INSERT INTO t VALUES( n_t (i) );
 11 
 12  EXCEPTION
 13    WHEN OTHERS THEN
 14      dbms_output.put_line( sqlerrm );
 15      FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
 16        dbms_output.put_line(SQL%BULK_EXCEPTIONS(i)
 17                             .ERROR_INDEX || ' : ' ||
 18                             SQLERRM(-1 * SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
 19      END LOOP;
 20 
 21  END;
 22  /
ORA-24381: error(s) in array DML
2 : ORA-01400: cannot insert NULL into ()
3 : ORA-02290: check constraint (.) violated
PL/SQL procedure successfully completed
SQL> SELECT * FROM t;
         I
----------
         1
SQL> rollback;
Rollback complete

Second method is using supplied package dbms_errlog.
SQL> drop table err$_t;
Table dropped
SQL> BEGIN
  2    dbms_errlog.create_error_log('t');
  3  END;
  4  /
PL/SQL procedure successfully completed
SQL> DECLARE
  2    TYPE t_t IS TABLE OF NUMBER;
  3    n_t t_t;
  4  BEGIN
  5    SELECT decode(rownum, 2, NULL, rownum) BULK COLLECT
  6      INTO n_t
  7      FROM user_tables
  8     WHERE rownum < 4;
  9    FORALL i IN n_t.FIRST .. n_t.LAST
 10      INSERT INTO t VALUES( n_t (i) ) log errors reject LIMIT unlimited;
 11 
 12  END;
 13  /
PL/SQL procedure successfully completed
SQL> SELECT * FROM t;
         I
----------
         1

Errors are logged.
SQL> SELECT t.ora_err_number$ errno, t.ora_err_mesg$ errmess FROM err$_t t;
     ERRNO ERRMESS
---------- --------------------------------------------------------------------------------
      1400 ORA-01400: cannot insert NULL into ("SYS"."T"."I")
      2290 ORA-02290: check constraint (SYS.CK_I) violated