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

Using DBMS_ERRORLOG Package

Oracle 10G ile birlikte gelen yeni paketlerden biridir DBMS_ERRORLOG.  Bu sayede yoğun işlemlerininizi, işlemleriniz kesilmeden yapabilirsiniz. Oluşan hataların ayrı bir yere aktarılması ile daha sonra oluşan hataları gözlemleyip, gerekli aksiyonları alabilirsiniz.

Aşağıda yaptığım örnekte 100000 kayıtlık bir insert işleminde olabilecek hatalardan dolayı bu paketin kullanılmasının avantajı belirtilmiştir.


Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as HR

SQL>
SQL> drop table error_log_test;

Table dropped

Executed in 0,016 seconds





Tablo Oluşturulur. İçine kayıt eklenir.

SQL> create table error_log_test( i number );

Table created

Executed in 0 seconds

SQL> DECLARE
  2    i NUMBER := 0;
  3  BEGIN
  4    FOR i IN 1 .. 100000 LOOP
  5      INSERT INTO error_log_test VALUES (i);
  6      IF MOD(i, 1000) = 0 THEN
  7        COMMIT;
  8      END IF;
  9    END LOOP;
 10 
 11  END;
 12  /

PL/SQL procedure successfully completed

Executed in 4,031 seconds

SQL> SELECT COUNT(*) FROM error_log_test;

  COUNT(*)
----------
    100000

Executed in 0,016 seconds





Kayıtların ekleneceği demo tablo oluşturulur.

SQL> drop table error_log_test_demo;

Table dropped

Executed in 0,219 seconds

SQL> drop table err$_error_log_test_demo;

drop table err$_error_log_test_demo

ORA-00942: table or view does not exist

SQL> create table error_log_test_demo( x number );

Table created

Executed in 0,016 seconds




Kısıtlar eklenir. Bu sayede hata oluşmasını sağlıyoruz.

SQL> alter table error_log_test_demo add constraint more_than_99990 check( x < 99990 );

Table altered

Executed in 0,015 seconds

SQL> alter table error_log_test_demo add constraint less_than_10 check( x > 10 );

Table altered

Executed in 0 seconds



dbms_errlog.create_error_log prosedürü ile hata esnasında gidilecek tablo oluşturulur.

SQL> BEGIN
  2    dbms_errlog.create_error_log('error_log_test_demo');
  3  END;
  4  /

PL/SQL procedure successfully completed

Executed in 0,016 seconds

SQL> describe err$_error_log_test_demo;
Name            Type           Nullable Default Comments
--------------- -------------- -------- ------- --------
ORA_ERR_NUMBER$ NUMBER         Y                        
ORA_ERR_MESG$   VARCHAR2(2000) Y                        
ORA_ERR_ROWID$  UROWID(4000)   Y                        
ORA_ERR_OPTYP$  VARCHAR2(2)    Y                        
ORA_ERR_TAG$    VARCHAR2(2000) Y                        
X               VARCHAR2(4000) Y                        



dbms_errlog kullanılmadan ekleme işlemi yapılmaya çalışılır.

SQL> insert into error_log_test_demo select * from error_log_test;

insert into error_log_test_demo select * from error_log_test

ORA-02290: check constraint (HR.LESS_THAN_10) violated



dbms_errlog ile ekleme işlemi yapılır. Hatalı kayıtlar err$_error_log_test_demo tablosuna aktarılır.

SQL> INSERT INTO error_log_test_demo
  2    SELECT * FROM error_log_test log errors reject LIMIT unlimited;

99979 rows inserted

Executed in 5,953 seconds

SQL> SELECT COUNT(*) FROM error_log_test_demo;

  COUNT(*)
----------
     99979

Executed in 0,016 seconds



Eklenemeyen kayıtlar ve neden eklenemdikleri buradan görülebilir.

SQL> select ed.ora_err_mesg$, ed.x from err$_error_log_test_demo ed ;

ORA_ERR_MESG$                                                       X
------------------------------------------------------           ----
ORA-02290: check constraint (HR.LESS_THAN_10) violated              1
ORA-02290: check constraint (HR.LESS_THAN_10) violated              2
ORA-02290: check constraint (HR.LESS_THAN_10) violated              3
ORA-02290: check constraint (HR.LESS_THAN_10) violated              4
ORA-02290: check constraint (HR.LESS_THAN_10) violated              5
ORA-02290: check constraint (HR.LESS_THAN_10) violated                6
ORA-02290: check constraint (HR.LESS_THAN_10) violated                7
ORA-02290: check constraint (HR.LESS_THAN_10) violated                8
ORA-02290: check constraint (HR.LESS_THAN_10) violated                9
ORA-02290: check constraint (HR.LESS_THAN_10) violated               10
ORA-02290: check constraint (HR.MORE_THAN_99990) violated         99990
ORA-02290: check constraint (HR.MORE_THAN_99990) violated         99991
ORA-02290: check constraint (HR.MORE_THAN_99990) violated         99992
ORA-02290: check constraint (HR.MORE_THAN_99990) violated         99993
ORA-02290: check constraint (HR.MORE_THAN_99990) violated         99994
ORA-02290: check constraint (HR.MORE_THAN_99990) violated         99995
ORA-02290: check constraint (HR.MORE_THAN_99990) violated         99996
ORA-02290: check constraint (HR.MORE_THAN_99990) violated         99997
ORA-02290: check constraint (HR.MORE_THAN_99990) violated         99998
ORA-02290: check constraint (HR.MORE_THAN_99990) violated         99999
ORA-02290: check constraint (HR.MORE_THAN_99990) violated        100000

21 rows selected

Executed in 0,047 seconds

SQL>