Hi,
I am trying to log all DML errors while performing ETL process. We encountered a problem in the process when one of the on delete cascade is missing in child tables but I was curious to know why we got that exception to the calling environment because we are logging all DML errors in err$_ tables. Our expectation is when we get child record found violation then that error will be logged into ERR$_ tables and process will carry on without any interruption but it interrupted in the middle and terminated. I can illustrate with below example
T1 -> T2 -> T3
T1 is parent and it is s root
Create table t1 (id number primary key, id2 number);
Create table t2(id number references t1(id) on delete cascade, id2 number);
create table t3 (id number references t2(id)); -- Missing on delete cascade
insert into t1 as select level, level from dual connect by level < 20;
insert into t2 as select level, level from dual connect by level < 20;
insert into t3 as select level from dual connect by level < 20;
exec dbms_errlog(t1);
exec dbms_errlog(t2);
exec dbms_errlog(t3);
delete from t1 where id = 1 log errors into err$_t1 reject limit unlimited; -- Child record found violation due to t3 raised but I am expecting this error will be trapped in log tables.
delete from t2 where id =1 log errors into err$_t2 reject limit unlimited; -- Got the same error child record violation. My expectation error will be logged into log tables.
I am using Oracle 11gR2.
Also, Please let me know if there is any restrictions to use DML error logging in DBMS_PARALLEL_EXECUTE.
Please advise
Thanks,
Umakanth