Hi All,
I am looking for a mechanism to track exceptions into one log table. Consider a scenario like there are 10 transactions and if an error do occur after say 5th transaction exception should be raised and also need to log the error details into error log (no transactions should be committed). I used autonomous_transaction pragma. But could achieve the desired result.
my procedure is like
create procedure Insert_log is
cnter number(2);
begin
cnter := 0;
insert into employee(emp_no, emp_name) values(10, 'pp') ;
cnter := 1;
insert into employee(emp_no, emp_name) values('', 'kk') ;
cnter := 2;
insert into employee(emp_no, emp_name) values(12, 'rr') ;
cnter := 3;
pragma autonomous_transaction;
insert into data_transfer_log(dtl_code, dtl_error) values(1, cnter);
commit;
exception
when others then
raise_application_error(-20001, 'Error Found in Statement : '|| cnter);
end;
/
The exception is raising the error message, But data is not written on error_log table. if i use autonomous_transaction pragma at the start all before error will get committed. I tried dml_error_logging with LOG ERRORS REJECT LIMIT UNLIMITED but didn't work
My database version : Oracle Database 11.2.0.2.0
Can anyone please help me in this..
Thanks&Regards
JaKes