oracle - Tracking failed transaction in bulk insert/update/delete -
i have following code structure
procedure .. pragma exception_init(dml_errors, -24381); l_errors number; begin -- busines logic forall table_1 delete; forall table_1 update; forall table_1 insert; forall table_2 insert; forall table_2 update; forall table_2 insert; exception when dml_errors --extract error indexes end;
each of forall loop deals separate table of array i.e., loop deleting table_1 deal table of table_1_u index pls_integer;
each forall loop has save exceptions keyword
now, how can extract "for" failed , "which record in index failed".
how can extract "for" failed
with call stack (format_error_backtrace) or implementing sort of steps:
procedure .. pragma exception_init(dml_errors, -24381); l_errors number; istep number; begin -- busines logic istep := 0; forall table_1 delete; istep := 1; forall table_1 update; istep := 2; forall table_1 insert; istep := 3; forall table_2 insert; istep := 4; forall table_2 update; istep := 5; forall table_2 insert; exception when dml_errors if istep = 0 elsif... end; end;
which record in index failed
by using bulk_exceptions cursor:
procedure .. pragma exception_init(dml_errors, -24381); l_errors number; istep number; begin -- busines logic istep := 0; forall table_1 delete; istep := 1; forall table_1 update; istep := 2; forall table_1 insert; istep := 3; forall table_2 insert; istep := 4; forall table_2 update; istep := 5; forall table_2 insert; exception when dml_errors in 1..sql%bulk_exceptions.count loop dbms_output.put_line('error #' || || ' occurred during '|| 'iteration #' || sql%bulk_exceptions(i).error_index); dbms_output.put_line('error message ' || sqlerrm(-sql%bulk_exceptions(i).error_code)); end loop; if istep = 0 elsif... end; end;
Comments
Post a Comment