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
