Monday, March 26, 2007

A common requirement when importing data into a table is to log any errors which occur due to constraint violation. This often leads to people processing data in a Cursor FOR LOOP and doing one insert at a time so that they can catch and log the error:

BEGIN
FOR source_rec IN (SELECT * FROM all_objects a)
LOOP
BEGIN
INSERT INTO t1
(col1
,col2
,col3)
VALUES
(source_rec.owner
,source_rec.object_name
,source_rec.subobject_name);
EXCEPTION
WHEN OTHERS THEN
--log error using PLVISION
plvexc.recngo;
END;
END LOOP;
END;

Another option is to use the EXCEPTIONS INTO clause of ENABLE constraint:
DECLARE
l_exc_count INTEGER;
BEGIN
--Disable unique constraint
EXECUTE IMMEDIATE 'ALTER TABLE t1 DISABLE CONSTRAINT UNQ_CONST1';
--Copy data (inc duplicates) from source table
INSERT INTO t1 (SELECT * FROM t2);
--The EXCEPTIONS table can be created using $ORACLE_HOME/rdbms/admin/utlexcpt.sql.
EXECUTE IMMEDIATE 'ALTER TABLE t1 ENABLE CONSTRAINT UNQ_CONST1 EXCEPTIONS INTO EXCEPTIONS';
--If constraint is violated it will still be disabled at this point
SELECT COUNT(1) INTO l_exc_count FROM t3;
IF l_exc_count > 0
THEN
--Copy invalid rows to errors table
EXECUTE IMMEDIATE 'CREATE TABLE errors_tab as SELECT * FROM t1,exceptions e where t1.rowid=e.row_id';

DELETE FROM t1
WHERE ROWID IN (SELECT minrid
FROM (SELECT object_id
,MIN(e.row_id) minrid
FROM EXCEPTIONS e
,t_dups t
WHERE t.ROWID = e.row_id
GROUP BY object_id));
END IF;
END;

Cool new feature in Oracle 10gR2 is the LOG ERRORS clause

Comment at asktom had the following:

insert /*+ APPEND */ into t select * from t2
LOG ERRORS REJECT LIMIT UNLIMITED;

Further details here

No comments: