Oracle Merge error handling / dbms_errlog example

The DBMS_ERRLOG package provides a procedure that enables you to create an error logging table so that DML operations can continue after encountering errors rather than abort and roll back. This enables you to save time and system resources.

Works only after Oracle10g Release 2

DROP TABLE catalog1;
CREATE TABLE catalog1 (ID NUMBER(3), item VARCHAR2 (20), price number);
INSERT INTO catalog1 VALUES(1, 'laptop', 800);
INSERT INTO catalog1 VALUES(2, 'iphone', 500);
insert into catalog1 values(3, 'camera', 400);

drop TABLE catalog2;
--To explicitly produce error column price is set to VARCHAR2
CREATE TABLE catalog2 (ID NUMBER(3), item VARCHAR2 (20), price VARCHAR2 (20));
INSERT INTO catalog2 VALUES(1, 'laptop', 100);
INSERT INTO catalog2 VALUES(2, 'iphone', 200);
insert into catalog2 values(3, 'camera', 'ABCDEFG'  );

drop table errlog;

BEGIN
--  dbms_errlog.create_error_log ('catalog1', 'errlog'); -- Use this if you need to use own error table name rather then default name
  dbms_errlog.create_error_log ('catalog1'); 
END;


/* Using dbms_errlog for MERGE*/
MERGE INTO catalog1 s1 USING catalog2 s2 ON (s1.id = s2.id)
WHEN MATCHED THEN UPDATE SET s1.price = s2.price 
--WHEN NOT MATCHED THEN INSERT (ID, item, price) VALUES (s2.ID, s2.item, s2.price)
--LOG errors INTO errlog ('Bad') REJECT LIMIT UNLIMITED
--LOG errors INTO errlog ('Bad') REJECT LIMIT 100
LOG ERRORS ('put comment') REJECT LIMIT UNLIMITED
;

/* Using dbms_errlog for update*/
BEGIN
   UPDATE catalog1
      SET price = 'AAAAAAAAAA'
      where rownum  < 2
      LOG errors ('put comment') REJECT LIMIT UNLIMITED;
END;


SELECT * FROM err$_catalog1; -- use this if you have not used error table name in dbms_errlog.create_error_log

SELECT * FROM errlog; -- use this if you have used error table name in dbms_errlog.create_error_log