DML Error Logging, instead of the 100,000 row update/insert/whatever failing because a single row doesn't quite work out, we can have the 99,999 successful rows go through and have the one bad row logged to a table!
Below is the example with data of all_objects into table de_big_tbl with an extra column id with Primary key constraint.
EXEC dbms_errlog.create_error_log('tbl');
DECLARE CURSOR c IS SELECT id,object_id, owner, object_name, subobject_name, data_object_id, DECODE( MOD(rownum,100000), 1, RPAD('*',20,'*'), object_type ) object_type, created, last_ddl_time, timestamp, status, temporary, generated, secondary FROM de_big_tbl; TYPE array IS TABLE OF c%ROWTYPE; l_data array; dml_errors EXCEPTION; PRAGMA EXCEPTION_INIT(dml_errors, -24381); l_errors NUMBER; l_errno NUMBER; l_msg VARCHAR2(4000); l_idx NUMBER; BEGIN OPEN c; LOOP FETCH c BULK COLLECT INTO l_data LIMIT 100; BEGIN FORALL i IN 1 .. l_data.COUNT SAVE EXCEPTIONS INSERT INTO tbl VALUES l_data(i); EXCEPTION WHEN dml_errors THEN l_errors := SQL%BULK_EXCEPTIONS.COUNT; FOR i IN 1 .. l_errors LOOP l_errno := SQL%BULK_EXCEPTIONS(i).error_code; l_msg := SQLERRM(-l_errno); l_idx := SQL%BULK_EXCEPTIONS(i).error_index; INSERT INTO err$_tbl ( ora_err_number$, ora_err_mesg$, ora_err_optyp$,id, object_id, owner, object_name, subobject_name, data_object_id, object_type, created, last_ddl_time, timestamp, status, temporary, generated, secondary ) VALUES ( l_errno, l_msg, 'I', l_data(l_idx).id, l_data(l_idx).object_id, l_data(l_idx).owner, l_data(l_idx).object_name, l_data(l_idx).subobject_name, l_data(l_idx).data_object_id, l_data(l_idx).object_type, l_data(l_idx).created, l_data(l_idx).last_ddl_time, l_data(l_idx).timestamp, l_data(l_idx).status, l_data(l_idx).temporary, l_data(l_idx).generated, l_data(l_idx).secondary ); END LOOP; END; EXIT WHEN c%NOTFOUND; END LOOP; CLOSE c; END;
SELECT ora_err_number$,ora_err_mesg$,ora_err_optyp$ FROM err$_tbl; Output : ora_err_number$ ora_err_mesg$ ora_err_optyp$ 1 ORA-00001: unique constraint (.) violated I 1 ORA-00001: unique constraint (.) violated I 1 ORA-00001: unique constraint (.) violated I 1 ORA-00001: unique constraint (.) violated I 1 ORA-00001: unique constraint (.) violated I 1 ORA-00001: unique constraint (.) violated I 1 ORA-00001: unique constraint (.) violated I 1 ORA-00001: unique constraint (.) violated I
The above code can also be replaced with:
exec dbms_errlog.create_error_log('tbl'); INSERT /*+ append */ INTO tbl SELECT id, owner, object_name, subobject_name, object_id, data_object_id, DECODE( mod(rownum,100000), 1, RPAD('*',20,'*'), object_type ) object_type, created, last_ddl_time, timestamp, status, temporary, generated, secondary FROM de_big_tbl LOG ERRORS REJECT LIMIT UNLIMITED;
Below are some other examples to use create error log for DMLs.
ALTER TABLE emp ADD CONSTRAINT emp_check_sal CHECK(sal > 50);
SELECT * FROM emp;
eid mid ename sal
5550228 1110299 Krish 450
5550229 1110299 Krush 100
5550609 1110299 Shyam 100
1110299 1110299 Ram 200
5556431 1110222 Abhi 300
1110222 1110222 Raju 400
EXEC dbms_errlog.create_error_log('emp');
UPDATE emp SET sal = sal - 50 WHERE sal BETWEEN 100 and 3000 LOG ERRORS ('Update Sal') REJECT LIMIT UNLIMITED; SELECT ora_err_number$, ora_err_optyp$, ora_err_tag$, eid, ora_err_mesg$ FROM err$_emp; ora_err_number$ ora_err_optyp$ ora_err_tag$ eid ora_err_mesg$ 2290 U Update Sal 5550229 ORA-02290: check constraint (DEEXAMS.EMP_CHECK_SAL) violated 2290 U Update Sal 5550609 ORA-02290: check constraint (DEEXAMS.EMP_CHECK_SAL) violated
If you like dEexams.com and would like to contribute, you can write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.